﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using VAdvantage.Apps;
using System.Text;
using VAdvantage.Model;
using System.Data;
using VAdvantage.DataBase;
using System.ComponentModel;
using VAdvantage.Utility;
using VAdvantage.DSProcessWorkflow;
using System.Threading;
using VAdvantage.Classes;
using VAdvantage.Grids;
using VAdvantage.Controls;
using System.IO;
using SL4PopupMenu;
using System.ServiceModel;
using System.ServiceModel.Channels;
using VAdvantage.ExcelImportService;
using System.Collections.ObjectModel;
using System.Runtime.InteropServices.Automation;
using System.Collections;

namespace VAdvantage.ImpExp
{
    public partial class ExcelImport : FormPanel
    {

        ExcelMapping _mappingDetail;
        ExcelMapping SavedDefaultValues = null;
        List<ExcelMappingPerTabDetail> _tabDetail;

        //Current window name
        string MWindowName = "";

        //Set primary key in excel. functionalty not covered as now :: //TODO
        //string m_ExcelPrimaryKey = "";

        //Stores all the mapped column in List Array
        List<int> m_MappedColumns = new List<int>();

        ////Store all the mapped tables in List Array
        //List<int> m_MappedTables = new List<int>();

        ////Stores Excel cols in List according to their index in excel sheet
        //List<int> m_ExcelColIndex = new List<int>();

        ////Store Excel cols in List according to their name in excel sheet
        //List<string> m_ExcelColName = new List<string>();

        ////List of identifier columns
        //List<int> m_identifierColumn = new List<int>();

        ////List of Tabs in a window
        //List<int> m_TabList = new List<int>();

        ////List all the tables (even not mapped tables)
        //List<int> m_AllTables = new List<int>();

        /*Stores Default values in Dictionary format according to the tables in a window.
         * DefaultValues will store Text values against their column name
        */
        List<Dictionary<String, Object>> DefaultValues = new List<Dictionary<String, Object>>();

        /*Stores Default values in Dictionary format according to the tables in a window.
         * DefaultValues will store ID values against their column name
        */
        List<Dictionary<String, Object>> DefaultValuesActual = new List<Dictionary<String, Object>>();

        //Keeps a check if import has been done once. if true, window will exit
        //bool IsImportDone = false;

        // Keeps the track of mapped column with excel column. 
        //Every new mapping is given a new name with incremented m_MappedIndex value
        int m_MapIndex = -1;

        //Stores the import info
        //ImportInfo info = null;
        OtherImportInfo info = null;


        bool loading = false;   //any process which is loading, just a flag

        PopupMenu infoWin = new PopupMenu();

        //List<List<int>> LocationMappedCols;
        //List<List<String>> LocationMappedColsName;
        //List<List<int>> LocationExcelCols;
        //List<List<String>> LocationMapText;

        int _selected_table_index = -1;

        ObservableCollection<CallbackDetail> _import;

        const string IDENTIFIER_STRING = "Identifier: ";
        PopupMenu removeDefault = null;
        PopupMenu removeMapping = null;
        public ExcelImport(int windowID, String windowName)
        {
            InitializeComponent();
            Utility.Envs.RegisterWindowForm(this);
            dic = (Dictionary<String, string>)Envs.GetCtx().GetMap();


            #region Search Textbox events
            txtSearchWindow.GotFocus += new RoutedEventHandler(txtSearchWindow_GotFocus);
            txtSearchWindow.Loaded += new RoutedEventHandler(txtSearchWindow_Loaded);
            txtSearchWindow.LostFocus += new RoutedEventHandler(txtSearchWindow_LostFocus);
            txtSearchWindow.KeyUp += new KeyEventHandler(txtSearchWindow_KeyUp);

            cmbTable.SelectionChanged += new SelectionChangedEventHandler(cmbTable_SelectionChanged);

            _import = new ObservableCollection<CallbackDetail>();
            lstImportLog.ItemsSource = _import;

            #endregion

            this.watermark = Msg.GetMsg("SearchWindow");
            //btnLoadFile.Text = Msg.GetMsg("Open");
            //btnUploadFile.Text = Msg.GetMsg("Load");

            infoWin.Opening += new RoutedEventHandler(infoWin_Opening);
            PopupMenuItem mnu = new PopupMenuItem(null, Msg.GetMsg(Envs.GetCtx(), "SetIdentifier"), null);
            mnu.ImageSource = Envs.LoadImageSource("check.png");
            mnu.Name = "SetIdentifier";
            mnu.Click += (ov, ev) =>
            {
                if (lstDBCols.Items.Count > 0)
                {
                    int colID = info.AllTables[cmbTable.SelectedIndex].ColumnDetail[lstDBCols.SelectedIndex].AD_Column_ID;
                    ExcelMappingDetail s = _mappingDetail.TabDetail[_selected_table_index].ColumnDetail.FirstOrDefault((a) => a.UniqueKey == lstDBCols.SelectedIndex);
                    if (s != null)
                    {
                        _mappingDetail.TabDetail[_selected_table_index].ColumnDetail.ForEach((a) => a.IsIdentifier = false);
                        s.IsIdentifier = true;
                        //m_identifierColumn[cmbTable.SelectedIndex] = (lstDBCols.SelectedItem as KeyNamePair).GetKey();
                        tbIdentifierValue.Text = IDENTIFIER_STRING + s.AD_Column_Name;
                    }
                    else
                    {
                        // s = _mappingDetail.TabDetail[_selected_table_index].ColumnDetail.FirstOrDefault((a) => a.AD_Column_ID == (lstDBCols.SelectedItem as KeyNamePair).GetKey());
                        s = _mappingDetail.TabDetail[_selected_table_index].ColumnDetail.FirstOrDefault((a) => a.AD_Column_ID == colID);
                        if (s != null)
                        {
                            _mappingDetail.TabDetail[_selected_table_index].ColumnDetail.ForEach((a) => a.IsIdentifier = false);
                            s.IsIdentifier = true;
                            //m_identifierColumn[cmbTable.SelectedIndex] = (lstDBCols.SelectedItem as KeyNamePair).GetKey();
                            tbIdentifierValue.Text = IDENTIFIER_STRING + s.AD_Column_Name;
                        }
                        else
                        {
                            ShowMessage.Error(Msg.GetMsg("MapIdentifier"), false);
                            return;
                        }
                    }
                }
            };

            infoWin.Items.Add(mnu);

            mnu = new PopupMenuItem(null, Msg.GetMsg(Envs.GetCtx(), "RemoveIdentifier"), null);
            mnu.ImageSource = Envs.LoadImageSource("cancel.png");
            mnu.Name = "RemoveIdentifier";
            mnu.Click += (ov, ev) =>
            {
                int colID = info.AllTables[cmbTable.SelectedIndex].ColumnDetail[lstDBCols.SelectedIndex].AD_Column_ID;
                ExcelMappingDetail s = _mappingDetail.TabDetail[_selected_table_index].ColumnDetail.FirstOrDefault((a) => a.UniqueKey == lstDBCols.SelectedIndex);
                if (s != null)
                {
                    s.IsIdentifier = false;
                    tbIdentifierValue.Text = IDENTIFIER_STRING + "None";
                }
                else
                {
                    //  s = _mappingDetail.TabDetail[_selected_table_index].ColumnDetail.FirstOrDefault((a) => a.AD_Column_ID == (lstDBCols.SelectedItem as KeyNamePair).GetKey());
                    s = _mappingDetail.TabDetail[_selected_table_index].ColumnDetail.FirstOrDefault((a) => a.AD_Column_ID == colID);
                    if (s != null)
                    {
                        s.IsIdentifier = false;
                        tbIdentifierValue.Text = IDENTIFIER_STRING + "None";
                    }
                }

                //if (m_identifierColumn[cmbTable.SelectedIndex].Equals((lstDBCols.SelectedItem as KeyNamePair).GetKey()))
                //{
                //    m_identifierColumn[cmbTable.SelectedIndex] = 0;
                //    tbIdentifierValue.Text = "None";
                //}
            };

            infoWin.Items.Add(mnu);

            mnu = new PopupMenuItem(null, Msg.GetMsg(Envs.GetCtx(), "LocationMapping"), null);
            mnu.ImageSource = Envs.LoadImageSource("Location10.png");
            mnu.Name = "LocationMapping";
            mnu.Click += (ov, ev) =>
            {
                if (location != null)
                    location = new MapLocation(_selectedAD_Window_ID, info.C_Location, _mappingDetail.ExcelCol, _tabDetail[_selected_table_index]);

                //if (LocationMappedCols != null)
                //    location.MappedColumns = LocationMappedCols[_selected_table_index];
                //if (LocationExcelCols != null)
                //    location.MappedColumns = LocationExcelCols[_selected_table_index];
                //if (LocationMapText != null)
                //    location.MappedList = LocationMapText[_selected_table_index];
                //if (LocationMappedColsName != null)
                //    location.MappedColumnName = LocationMappedColsName[_selected_table_index];

                location.Closed += (ov1, ev1) =>
                {
                    //LocationMappedCols[_selected_table_index] = location.MappedColumns;
                    //LocationExcelCols[_selected_table_index] = location.ExcelColIndex;
                    //LocationMapText[_selected_table_index] = location.MappedList;
                    //LocationMappedColsName[_selected_table_index] = location.MappedColumnName;

                    //IsLocation = false;
                };

                location.Show();
            };

            infoWin.Items.Add(mnu);

            infoWin.AddTrigger(TriggerTypes.RightClick, lstDBCols);


            removeDefault = new PopupMenu();
            PopupMenuItem removeDefaultItem = new PopupMenuItem(null, Msg.GetMsg(Envs.GetCtx(), "RemoveDefault"), null);
            removeDefaultItem.ImageSource = Envs.LoadImageSource("cancel.png");
            removeDefaultItem.Name = "RemoveDefaultValue";
            removeDefaultItem.Click += new RoutedEventHandler(removeDefaultItem_Click);
            removeDefault.Items.Add(removeDefaultItem);

            removeMapping = new PopupMenu();
            PopupMenuItem removeMapedItem = new PopupMenuItem(null, Msg.GetMsg(Envs.GetCtx(), "RemoveMapping"), null);
            removeMapedItem.ImageSource = Envs.LoadImageSource("cancel.png");
            removeMapedItem.Name = "RemoveMappedValue";
            removeMapedItem.Click += new RoutedEventHandler(removeMapedItem_Click);
            removeMapping.Items.Add(removeMapedItem);
            //removeDefaultItem.Click += (ov, ev) =>
            //{
            //    //if (lstDefaultValues.Items.Count > 0)
            //    //{
            //    //    //m_DefaultValueSelected = lstDefaultValues.SelectedItem.ToString();
            //    //    //tbExcelPrimaryValue.Text = m_ExcelPrimaryKey;

            //    //    try
            //    //    {
            //    //        Dictionary<String, Object> _dic = DefaultValues[cmbTable.SelectedIndex];
            //    //        int value = (int)StoredDefaultValueList[cmbTable.SelectedIndex][lstDefaultValues.SelectedIndex];
            //    //        string columnToRemove = _dic.ElementAt(value).Key;

            //    //        DefaultValues[cmbTable.SelectedIndex][columnToRemove] = null;
            //    //        DefaultValuesActual[cmbTable.SelectedIndex][columnToRemove] = null;
            //    //        StoredDefaultValueList[cmbTable.SelectedIndex].RemoveAt(lstDefaultValues.SelectedIndex);
            //    //        lstDefaultValues.Items.RemoveAt(lstDefaultValues.SelectedIndex);
            //    //    }
            //    //    catch
            //    //    {
            //    //        ShowMessage.Error(Msg.GetMsg("UnExpectedErrorOccured"), false);
            //    //        return;
            //    //    }
            //    //}
            //};


            // removeDefault.AddTrigger(TriggerTypes.RightClick, localDefulttreeView.Items);



            if (windowID > 0)
            {
                lstSearchedWindows.Items.Add(new KeyNamePair(windowID, windowName));
                lstSearchedWindows.SelectedIndex = 0;
            }

            loadWindowsDetailsdown.IsEnabled = false;
            btnExportLog.IsEnabled = false;
            cmbTable.IsEnabled = false;

            SetCulture();
            this.SetTitle(Msg.GetMsg("ImportFromExcel"));
        }

        void removeMapedItem_Click(object sender, RoutedEventArgs e)
        {
            //throw new NotImplementedException();
            RemoveMapping();
        }

        void removeDefaultItem_Click(object sender, RoutedEventArgs e)
        {
            //throw new NotImplementedException();
            try
            {
                VTreeNode node = localDefulttreeView.SelectedItem as VTreeNode;


                Dictionary<String, Object> _dic = DefaultValues[cmbTable.SelectedIndex];
                // int value = (int)StoredDefaultValueList[cmbTable.SelectedIndex][lstDefaultValues.SelectedIndex];
                string columnToRemove = "";//_dic.ElementAt(value).Key;
                if (node != null)
                {
                    columnToRemove = node.ColName;
                }

                columnToRemove = columnToRemove.Substring(0, columnToRemove.Length - 1);

                DefaultValues[cmbTable.SelectedIndex][columnToRemove] = null;
                DefaultValuesActual[cmbTable.SelectedIndex][columnToRemove] = null;
                //StoredDefaultValueList[cmbTable.SelectedIndex].RemoveAt(lstDefaultValues.SelectedIndex);
                // StoredDefaultValueList[cmbTable.SelectedIndex].RemoveAt(;
                int index = int.Parse((localDefulttreeView.SelectedItem as VTreeNode).Name);
                int tabIndex = ((localDefulttreeView.SelectedItem as VTreeNode).Parent as VTreeNode).SeqNo;

                ExcelMappingDetail todelete = SavedDefaultValues.TabDetail[tabIndex].ColumnDetail.FirstOrDefault((a) => a.PrimaryKey == index);
                if (todelete == null)
                    todelete = SavedDefaultValues.TabDetail[tabIndex].ColumnDetail.FirstOrDefault((a) => a.UniqueKey == index);
                if (todelete == null)
                    todelete = SavedDefaultValues.TabDetail[tabIndex].ColumnDetail.FirstOrDefault((a) => a.AD_Column_ID == index);

                if (todelete != null)
                {
                    if (todelete.IsIdentifier)
                    {
                        ExcelMappingDetail setidnt = info.AllTables[tabIndex].ColumnDetail.FirstOrDefault((a) => a.IsIdentifier == true);
                        if (setidnt != null)
                            tbIdentifierValue.Text = IDENTIFIER_STRING + setidnt.AD_Column_Name;
                        else
                            tbIdentifierValue.Text = IDENTIFIER_STRING + "None";
                    }

                    if (index < 100)
                        SavedDefaultValues.TabDetail[tabIndex].ColumnDetail.Remove(todelete);
                    else
                        todelete.ToDelete = true;

                }
                SetDBColDefaultState();
                ((localDefulttreeView.SelectedItem as VTreeNode).Parent as VTreeNode).Items.Remove(localDefulttreeView.SelectedItem);

            }
            catch
            {
            }
        }

        void infoWin_Opening(object sender, RoutedEventArgs e)
        {
            if (_mappingDetail.TabDetail[_selected_table_index].ColumnDetail == null)
            {
                infoWin.PopupMenuItem(0).IsEnabled = false;
                infoWin.PopupMenuItem(1).IsEnabled = false;
            }
            else
            {
                infoWin.PopupMenuItem(0).IsEnabled = true;
                infoWin.PopupMenuItem(1).IsEnabled = true;
            }

            //if (IsLocation)
            //    infoWin.PopupMenuItem(2).IsEnabled = true;
            //else
            //    infoWin.PopupMenuItem(2).IsEnabled = false;
        }

        #region Search Window
        /// <summary>
        /// Gets the Windows available in database on the basis of search key
        /// </summary>
        /// <param name="_searchKey">Search Key to be inputted</param>
        /// <returns>Window detail in KeyValuePair</returns>
        public KeyNamePair[] GetWindowDetail(String _searchKey)
        {

            //Basic queries to fetch window records
            StringBuilder _sql = new StringBuilder("SELECT AD_Window.Name, AD_Window.AD_Window_ID ")
                                            .Append("from AD_Window AD_Window Inner Join AD_Window_Access AD_Window_Access ")
                                            .Append("ON AD_Window.AD_Window_ID = AD_Window_Access.AD_Window_ID ")
                                            .Append("WHERE Upper(AD_Window.Name) like '%" + _searchKey.ToUpper() + "%'")
                                            .Append(" AND AD_Window.IsActive = 'Y'")
                                            .Append(" AND AD_Window_Access.AD_Role_ID = " + Envs.GetCtx().GetAD_Role_ID())
                                            .Append(" AND AD_Window_Access.IsReadWrite = 'Y'");

            //Get all the windows according to their role
            String _finalSql = MRole.GetDefault().AddAccessSQL(_sql.ToString(), "AD_Window", true, false);

            List<KeyNamePair> _listWindow = new List<KeyNamePair>();

            //execute the query
            IDataReader dr = DB.ExecuteReader(_finalSql);
            while (dr.Read())
            {
                //store each parsed value in temp KeyNamePair
                KeyNamePair _keyValye
                    = new KeyNamePair(int.Parse(dr[1].ToString()), dr[0].ToString());

                _listWindow.Add(_keyValye);
            }
            dr.Close();

            //return array to the list
            return _listWindow.ToArray();
        }


        /// <summary>
        /// Event will be called when search button is clicked
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void OnSearchButtonClick()
        {
            try
            {
                loading = true;
                SetBusy(true, loadWindows);
                BackgroundWorker bgw = new BackgroundWorker();
                KeyNamePair[] _searchedWindowDetail = null;

                String _searchKey = txtSearchWindow.Text;
                //bool pass = false;

                bgw.DoWork += (ov, ev) =>
                    {
                        _searchedWindowDetail = GetWindowDetail(_searchKey);

                        //while (!pass)
                        //Thread.Sleep(0);
                    };

                bgw.RunWorkerCompleted += (ov, ev) =>
                    {
                        if (ev.Error == null)
                        {
                            if (_searchedWindowDetail == null) return;  //do not do anything if the result is null

                            lstSearchedWindows.Items.Clear();   //clear old serach
                            for (int c = 0; c < _searchedWindowDetail.Count(); c++)
                            {
                                //add searched records in listbox
                                lstSearchedWindows.Items.Add(_searchedWindowDetail[c]);
                            }
                        }

                        SetBusy(false, loadWindows);
                        loading = false;
                    };

                bgw.RunWorkerAsync();   //run the background worker
            }
            catch (Exception ex)
            {
                ShowMessage.Error(ex.Message, false);
                SetBusy(false, loadWindows);
                return;
            }
        }
        #endregion

        void SetBusy(bool isBusy, Object control)
        {
            BusyIndicator obj = (control as BusyIndicator);
            if (isBusy)
            {
                obj.IsBusy = true;
                lstSearchedWindows.IsEnabled = false;
                txtSearchWindow.IsEnabled = false;

                cmbTable.IsEnabled = false;

            }
            else
            {
                obj.IsBusy = false;

                lstSearchedWindows.IsEnabled = true;
                txtSearchWindow.IsEnabled = true;
                if (!loadWindowsDetails.IsBusy && !loadWindowsDetailsdown.IsBusy)
                {
                    if (loadWindowsDetailsdown.IsEnabled && loadWindowsDetails.IsEnabled)
                        cmbTable.IsEnabled = true;
                }
            }
        }

        #region Watermark on search textbox
        private string watermark;
        public string WaterMark
        {
            set
            {
                watermark = value;
            }

            get
            {
                return watermark;
            }
        }

        public string Text
        {
            get
            {
                if (txtSearchWindow.Text == this.watermark)
                {
                    return "";
                }
                else
                {
                    return txtSearchWindow.Text;
                }
            }
            set
            {
                DisableWaterMark();
                txtSearchWindow.Text = value;
            }
        }

        private void SetWaterMark()
        {
            txtSearchWindow.Foreground = new SolidColorBrush(Colors.LightGray);
            txtSearchWindow.Text = this.watermark;
        }

        private void DisableWaterMark()
        {
            txtSearchWindow.Text = "";
            txtSearchWindow.GotFocus -= txtSearchWindow_GotFocus;
            txtSearchWindow.Foreground = new SolidColorBrush(Colors.Black);
        }


        void txtSearchWindow_GotFocus(object sender, RoutedEventArgs e)
        {
            DisableWaterMark();
        }


        void txtSearchWindow_Loaded(object sender, RoutedEventArgs e)
        {
            if (!string.IsNullOrEmpty(this.watermark))
            {
                txtSearchWindow.GotFocus += new RoutedEventHandler(txtSearchWindow_GotFocus);
                txtSearchWindow.LostFocus += new RoutedEventHandler(txtSearchWindow_LostFocus);
                SetWaterMark();
            }
        }

        void txtSearchWindow_LostFocus(object sender, RoutedEventArgs e)
        {
            if (string.IsNullOrEmpty(txtSearchWindow.Text))
            {
                SetWaterMark();
                txtSearchWindow.GotFocus += new RoutedEventHandler(txtSearchWindow_GotFocus);
            }
        }


        #endregion

        void txtSearchWindow_KeyUp(object sender, KeyEventArgs e)
        {
            if (e.Key == Key.Enter)
            {
                OnSearchButtonClick();
            }
        }

        void ClearOldDetail()
        {
            location = null;

            //Current window name
            MWindowName = "";

            //Set primary key in excel. functionalty not covered as now :: //TODO
            //m_ExcelPrimaryKey = "";

            //Stores all the mapped column in List Array
            m_MappedColumns.Clear();

            ////Store all the mapped tables in List Array
            //m_MappedTables.Clear();

            ////Stores Excel cols in List according to their index in excel sheet
            //m_ExcelColIndex.Clear();

            ////Store Excel cols in List according to their name in excel sheet
            //m_ExcelColName.Clear();

            ////List of identifier columns
            //m_identifierColumn.Clear();

            ////List of Tabs in a window
            //m_TabList.Clear();

            ////List all the tables (even not mapped tables)
            //m_AllTables.Clear();

            /*Stores Default values in Dictionary format according to the tables in a window.
             * DefaultValues will store Text values against their column name
            */
            DefaultValues.Clear();

            /*Stores Default values in Dictionary format according to the tables in a window.
             * DefaultValues will store ID values against their column name
            */
            DefaultValuesActual.Clear();

            _gridFieldsArray.Clear();

            _findFields = null;

            //wVO = null;

            _mWindow = null;

            _mTab = null;

            SelectedColumn = "";

            _import.Clear();

            //lstDefaultValues.Items.Clear();
        }

        int _selectedAD_Window_ID = 0;

        Dictionary<int, OtherImportInfo> _windowCache = new Dictionary<int, OtherImportInfo>();

        bool load_window_details = false;

        private void OnWindowListChange(object sender, SelectionChangedEventArgs e)
        {
            try
            {
                if (loading)
                    return;

                if (lstSearchedWindows.SelectedIndex < 0)      //check if any record is selected
                    return; //if not, do not process further

                ClearOldDetail();   //clear all the old mapping details

                _selectedAD_Window_ID = (lstSearchedWindows.SelectedItem as KeyNamePair).GetKey();  //extraxt key of selected item
                MWindowName = (lstSearchedWindows.SelectedItem as KeyNamePair).GetName();  //extraxt key of selected item

                if (_selectedAD_Window_ID <= 0)
                    return; //if key is less than 0, do not process further

                load_window_details = true;

                info = null;

                if (_windowCache.ContainsKey(_selectedAD_Window_ID))
                {
                    info = _windowCache[_selectedAD_Window_ID];
                }


                SetBusy(true, loadWindowsDetails);  //set the busy sign to true

                Dictionary<string, string> dic = (Dictionary<String, string>)Envs.GetCtx().GetMap();        //create context

                lstDBCols.Items.Clear();

                //LocationMappedCols = null;
                //LocationExcelCols = null;
                //LocationMapText = null;

                if (_mappingDetail != null)
                    _mappingDetail = null;

                _mappingDetail = new ExcelMapping();
                _mappingDetail.AD_Window_ID = _selectedAD_Window_ID;
                _mappingDetail.CtxDic = dic;

                if (SavedDefaultValues != null)
                    SavedDefaultValues = null;

                SavedDefaultValues = new ExcelMapping();
                SavedDefaultValues.AD_Window_ID = _selectedAD_Window_ID;
                SavedDefaultValues.CtxDic = dic;

                if (_tabDetail != null)
                    _tabDetail.Clear();
                else
                    _tabDetail = new List<ExcelMappingPerTabDetail>();


                BackgroundWorker bgw = new BackgroundWorker();
                bgw.DoWork += (ov, ev) =>
                    {
                        SavedMapping = GetMapping();
                        if (info == null)
                        {
                            //any process which is to be passed. Just a flag
                            bool pass = false;
                            var client = AEnv.GetExcelImportService();
                            client.LoadStartupInfoCompleted += (a, b) =>
                            {

                                if (b.Error != null)
                                {
                                    Classes.ShowMessage.Error("Error?", true, b.Error.Message);
                                }
                                if (b.Result != null)
                                    info = b.Result;

                                //if (!String.IsNullOrEmpty(info.ServiceError))
                                //{
                                //    Classes.ShowMessage.Error("Error?", true, info.ServiceError);
                                //    info = null;
                                //}

                                client.CloseAsync();
                                pass = true;
                            };

                            client.LoadStartupInfoAsync(_selectedAD_Window_ID, dic);
                            //client.CloseAsync();

                            while (!pass)
                                Thread.Sleep(0);
                        }
                    };

                bgw.RunWorkerCompleted += (ov, ev) =>
                    {
                        cmbTable.Items.Clear();

                        cmbMapping.Items.Clear();
                        OnNewMode(false);
                        if (SavedMapping != null)
                        {
                            for (int s = 0; s < SavedMapping.Count(); s++)
                            {
                                cmbMapping.Items.Add(SavedMapping[s]);
                            }
                        }

                        if (info != null)
                        {
                            if (String.IsNullOrEmpty(info.AnyError))
                            {
                                if (!_windowCache.ContainsKey(_selectedAD_Window_ID))
                                    _windowCache.Add(_selectedAD_Window_ID, info);
                                else
                                    _windowCache[_selectedAD_Window_ID] = info;

                                localtreeView.Items.Clear();
                                lstExcelCols.Items.Clear();

                                localDefulttreeView.Items.Clear();


                                VTreeNode root = new VTreeNode("folder.png", MWindowName);
                                root.Expanded += (a, b) =>
                                {
                                    root.ImageKey = "folder-open.png";
                                };

                                //root.ImageKey = "folder.png";
                                root.IsExpanded = true;
                                VTreeNode rootDefalt = new VTreeNode("folder.png", MWindowName);
                                rootDefalt.Expanded += (a, b) =>
                                {
                                    root.ImageKey = "folder-open.png";
                                };
                                rootDefalt.IsExpanded = true;
                                //root.Header = MWindowName;
                                localtreeView.Items.Add(root);


                                localDefulttreeView.Items.Add(rootDefalt);

                                int seqno = 0;
                                foreach (ExcelMappingPerTabDetail pair in info.AllTables)
                                {
                                    VTreeNode vnode = new VTreeNode("mWindow.png", pair.TableName);
                                    vnode.MouseRightButtonUp -= vnode_MouseRightButtonUp;
                                    vnode.MouseRightButtonDown -= vnode_MouseRightButtonDown;
                                    vnode.MouseRightButtonDown += new MouseButtonEventHandler(vnode_MouseRightButtonDown);
                                    vnode.MouseRightButtonUp += new MouseButtonEventHandler(vnode_MouseRightButtonUp);

                                    VTreeNode vnodeDefault = new VTreeNode("mWindow.png", pair.TableName);


                                    vnodeDefault.MouseLeftButtonDown -= vnodeDefault_MouseLeftButtonDown;
                                    vnodeDefault.MouseLeftButtonDown += new MouseButtonEventHandler(vnodeDefault_MouseLeftButtonDown);
                                    vnodeDefault.MouseRightButtonUp -= vnodeDefault_MouseRightButtonUp;
                                    vnodeDefault.MouseRightButtonUp += new MouseButtonEventHandler(vnodeDefault_MouseRightButtonUp);

                                    vnode.SeqNo = seqno;
                                    vnodeDefault.SeqNo = seqno;
                                    root.Items.Add(vnode);
                                    rootDefalt.Items.Add(vnodeDefault);
                                    cmbTable.Items.Add(new KeyNamePair(pair.AD_Tab_ID, pair.TableName));

                                    _gridFieldsArray.Add(null);

                                    //Store default values in dictionary
                                    DefaultValues.Add(new Dictionary<string, Object>());
                                    DefaultValuesActual.Add(new Dictionary<string, Object>());
                                    StoredDefaultValueList.Add(new List<int?>());
                                    //m_identifierColumn.Add(0);
                                    //m_AllTables.Add(ParseDuplicateValue(pair.Key.ToString()));

                                    //LocationExcelCols.Add(new List<int>());
                                    //LocationMappedCols.Add(new List<int>());
                                    //LocationMapText.Add(new List<string>());
                                    //LocationMappedColsName.Add(new List<string>());

                                    _tabDetail.Add(new ExcelMappingPerTabDetail());     //new code
                                    _tabDetail[seqno].AD_Tab_ID = pair.AD_Tab_ID;
                                    _tabDetail[seqno].AD_Table_ID = pair.AD_Table_ID;
                                    _tabDetail[seqno].TableName = pair.TableName;

                                    seqno++;
                                }

                                _mappingDetail.TabDetail = _tabDetail;
                                _mappingDetailOld = _mappingDetail;

                                SavedDefaultValues.TabDetail = _tabDetail;
                                cmbTable.SelectedIndex = -1;


                                SetBusy(false, loadWindowsDetails);

                                btnMap.IsEnabled = true;
                                //btnRemoveMap.IsEnabled = true;
                                btnSave.IsEnabled = true;
                                btnLoadFile.IsEnabled = true;
                                btnImport.IsEnabled = true;

                                load_window_details = false;

                                ImportDetailTab.SelectedIndex = 0;

                                cmbTable.SelectedIndex = 0;
                            }
                            else
                            {
                                ShowMessage.Error(info.AnyError, false);
                                info.AnyError = "";
                                SetBusy(false, loadWindowsDetails);
                                return;
                            }
                        }

                    };

                bgw.RunWorkerAsync();
            }
            catch (Exception ex)
            {
                ShowMessage.Error(ex.Message, false);
                SetBusy(false, loadWindowsDetails);
                return;
            }
        }

        void vnode_MouseRightButtonUp(object sender, MouseButtonEventArgs e)
        {
            //throw new NotImplementedException();
            try
            {
                VisualTreeHelper.FindElementsInHostCoordinates(e.GetPosition(null), (sender as VTreeNode)).OfType<VTreeNode>().First().IsSelected = true;
            }
            catch { }
        }

        void vnode_MouseRightButtonDown(object sender, MouseButtonEventArgs e)
        {
            //throw new NotImplementedException();
            try
            {
                VisualTreeHelper.FindElementsInHostCoordinates(e.GetPosition(null), (sender as VTreeNode)).OfType<VTreeNode>().First().IsSelected = true;
            }
            catch { }
        }

        void vnodeDefault_MouseRightButtonUp(object sender, MouseButtonEventArgs e)
        {
            try
            {
                VisualTreeHelper.FindElementsInHostCoordinates(e.GetPosition(null), (sender as VTreeNode)).OfType<VTreeNode>().First().IsSelected = true;
            }
            catch { }
        }

        void vnodeDefault_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
        {
            try
            {
                VisualTreeHelper.FindElementsInHostCoordinates(e.GetPosition(null), (sender as VTreeNode)).OfType<VTreeNode>().First().IsSelected = true;
            }
            catch { }
        }



        private void FillDefaultList()
        {
            // lstDefaultValues.Items.Clear();
            int CurrenntTableIndex = cmbTable.SelectedIndex;
            Dictionary<String, object> defaults = DefaultValues[CurrenntTableIndex];

            foreach (KeyValuePair<String, Object> keys in defaults)
            {
                if (keys.Value != null)
                {
                    //  lstDefaultValues.Items.Add(keys.Key + " <==> " + keys.Value);
                }
            }
        }

        MTab tab;

        int AD_Window_ID = 0;

        //GridWindowVO wVO;

        GridWindow _mWindow;

        GridTab _mTab;

        GridField[] _findFields;

        List<GridField[]> _gridFieldsArray = new List<GridField[]>();

        String SelectedColumn;


        private void OnTableChange()
        {
            SetBusy(true, loadTableColumns);
            BackgroundWorker bgw = new BackgroundWorker();
            int CurrentTab_ID = info.AllTables[cmbTable.SelectedIndex].AD_Tab_ID;
            int CurrentSelectedTableIndex = cmbTable.SelectedIndex;
            _selected_table_index = CurrentSelectedTableIndex;
            int windowNo = Envs.CreateWindowNo(this);
            //_IsFetchingColumn = true;

            // bool actualUploadState = btnUploadFile.IsEnabled;
            bool actualImportState = btnImport.IsEnabled;

            // btnUploadFile.IsEnabled = false;
            btnImport.IsEnabled = false;

            bgw.DoWork += (q, w) =>
            {
                if (_gridFieldsArray[CurrentSelectedTableIndex] == null)
                {
                    tab = MTab.Get(Envs.GetCtx(), CurrentTab_ID);
                    _mappingDetail.TabDetail[_selected_table_index].AD_Tab_ID = CurrentTab_ID;
                    _mappingDetail.TabDetail[_selected_table_index].AD_Table_ID = tab.GetAD_Table_ID();

                    SavedDefaultValues.TabDetail[_selected_table_index].AD_Tab_ID = CurrentTab_ID;
                    SavedDefaultValues.TabDetail[_selected_table_index].AD_Table_ID = tab.GetAD_Table_ID();

                    AD_Window_ID = tab.GetAD_Window_ID();

                    GridWindowVO wVO = AEnv.GetMWindowVO(windowNo, AD_Window_ID, 0);

                    if (wVO == null)
                    {
                    }    //do something if GridWindow is found null
                    else
                    {

                        _mWindow = new GridWindow(wVO);
                        _mTab = _mWindow.GetTabByID(tab.GetAD_Tab_ID());

                        if (_mTab != null)
                        {
                            _findFields = _mTab.GetFields();

                            _gridFieldsArray[CurrentSelectedTableIndex] = _findFields;
                        }
                    }
                }
                else
                    _findFields = _gridFieldsArray[CurrentSelectedTableIndex];

            };

            bgw.RunWorkerCompleted += (q, w) =>
            {
                if (_mTab == null)
                {
                    ShowMessage.Error(Msg.GetMsg("ErrorLoadingTabDetail"), false);
                    //this.DialogResult = false;
                }
                else
                {
                    OnLoad(windowNo);
                    SetBusy(false, loadTableColumns);
                    FillDefaultList();
                }

                // btnUploadFile.IsEnabled = actualUploadState;

                btnImport.IsEnabled = actualImportState;

                if (lstExcelCols.Items.Count > 0)
                    btnImport.IsEnabled = true;

                //_IsFetchingColumn = false;
            };

            bgw.RunWorkerAsync();
        }


        public void OnLoad(int _curWindowNo)
        {
            List<MLookup> lookups = DataBase.DBQ.Get().GetWindowLookup(_curWindowNo);
            if (lookups != null)
            {

                //new Thread(new ThreadStart(() =>
                //{
                for (int i = 0; i < lookups.Count; i++)
                {
                    lookups[i].InitializeSLOtherTabs();
                }
                DataBase.DBQ.Get().Dispose(_curWindowNo);
                //})).Start();
            }
        }


        void cmbTable_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (load_window_details)
                return;

            lstDBCols.Items.Clear();
            //lstDBCols = new ListBox();
            Dictionary<int, string> dic = info.AllTables[cmbTable.SelectedIndex].ColumnDetail.ToDictionary(a => a.AD_Column_ID, a => a.AD_ColumnField_Name);
            Dictionary<int, string> dic1 = info.AllTables[cmbTable.SelectedIndex].ColumnDetail.ToDictionary(a => a.AD_Column_ID, a => a.AD_Column_Name);

            Dictionary<String, Object> temp = new Dictionary<string, object>(); //temp store for default values
            Dictionary<String, Object> tempActual = new Dictionary<string, object>(); //temp store for default values
            int i = 0;
            foreach (KeyValuePair<int, string> pair in dic)
            {



                //lstDBCols.Items.Add(new KeyNamePair(pair.Key, pair.Value));
                TextBlock txt = new TextBlock();
                // txt.Name = pair.Key.ToString();
                txt.Text = pair.Value;
                lstDBCols.Items.Add(txt);//new KeyNamePair(pair.Key, pair.Value));
                //if (info.AllTables[cmbTable.SelectedIndex].ColumnDetail[i].AD_Column_Name == "Created"
                //   || info.AllTables[cmbTable.SelectedIndex].ColumnDetail[i].AD_Column_Name == "CreatedBy"
                //   || info.AllTables[cmbTable.SelectedIndex].ColumnDetail[i].AD_Column_Name == "Updated"
                //   || info.AllTables[cmbTable.SelectedIndex].ColumnDetail[i].AD_Column_Name == "UpdatedBy"
                //   || info.AllTables[cmbTable.SelectedIndex].ColumnDetail[i].AD_Column_Name == "AD_Client_ID"
                //   || info.AllTables[cmbTable.SelectedIndex].ColumnDetail[i].AD_Column_Name == "AD_Org_ID")
                //{
                //    (lstDBCols.Items[i] as TextBlock).Visibility = Visibility.Collapsed;
                //    (lstDBCols.Items[i] as TextBlock).Height = 0;                    
                //    (lstDBCols.Items[i] as UIElement).Visibility = Visibility.Collapsed;
                //}



                //add column detail and their default values
                String pairKey = dic1[pair.Key];
                if (DefaultValues[cmbTable.SelectedIndex].ContainsKey(pairKey))
                {
                    Object setValue = DefaultValues[cmbTable.SelectedIndex][pairKey];
                    Object setValueActual = DefaultValuesActual[cmbTable.SelectedIndex][pairKey];
                    temp.Add(pairKey, setValue);
                    tempActual.Add(pairKey, setValueActual);
                }
                else
                {
                    temp.Add(pairKey, null);
                    tempActual.Add(pairKey, null);
                }
                //}
                i++;

                //if (dic.TryGetValue(m_identifierColumn[cmbTable.SelectedIndex], out identifierColName))
                //    tbIdentifierValue.Text = identifierColName;
                //else
                //    tbIdentifierValue.Text = "None";
            }
            SetDBColDefaultState();
            SetDBColMappedState();
            string identifierColName = IDENTIFIER_STRING + "None";

            ExcelMappingDetail emd = null;

            if (!IsNull(_mappingDetail.TabDetail[cmbTable.SelectedIndex].ColumnDetail))
                emd = _mappingDetail.TabDetail[cmbTable.SelectedIndex].ColumnDetail.FirstOrDefault((a) => a.IsIdentifier == true);



            if (IsNull(emd))
            {
                //emd = info.AllTables[cmbTable.SelectedIndex].ColumnDetail.FirstOrDefault((a) => a.IsIdentifier == true);
                //if (emd != null)
                //{
                //    tbIdentifierValue.Text = IDENTIFIER_STRING + emd.AD_Column_Name;
                //}
                //else
                tbIdentifierValue.Text = identifierColName;
            }
            else
            {
                tbIdentifierValue.Text = IDENTIFIER_STRING + emd.AD_Column_Name;
            }

            DefaultValues[cmbTable.SelectedIndex] = temp;
            DefaultValuesActual[cmbTable.SelectedIndex] = tempActual;

            if (!loading)
                OnTableChange();
        }


        private bool IsNull(Object ob)
        {

            if (ob == null)
                return true;

            return false;
        }

        private void OnColumnChanged()
        {
            if (lstDBCols.SelectedItem == null)
            {
                SetControl(false, null);
                return;
            }


            SelectedColumn = info.AllTables[_selected_table_index].ColumnDetail.ElementAt(lstDBCols.SelectedIndex).AD_Column_Name;

            if ((SelectedColumn.StartsWith("AD_Client_ID", StringComparison.OrdinalIgnoreCase)) ||
                (SelectedColumn.StartsWith("AD_Org_ID", StringComparison.OrdinalIgnoreCase)))
            {
                SetControl(false, null);
                return;
            }

            if (info.AllTables[_selected_table_index].TableName.Trim().EndsWith(")"))
            {
                string name = info.AllTables[_selected_table_index].TableName;
                if (name.Contains("(") && name.Contains(")"))
                {
                    name = name.Substring(name.IndexOf("(") + 1);
                    if (name.Contains(")"))
                    {
                        name = name.Replace(")", "");
                        name = name.Trim();
                    }
                }

                if ((name + "_ID").ToUpper().Equals(SelectedColumn.ToUpper()))
                {
                    SetControl(false, null);
                    return;
                }
            }

            GridField field = GetTargetMField(SelectedColumn);

            if (field != null)
            {
                if (field.GetColumnName().Equals("C_Location_ID", StringComparison.OrdinalIgnoreCase))
                {
                    SetControl(false, null);
                    //IsLocation = true;
                    infoWin.PopupMenuItem(2).IsEnabled = true;

                    if (location == null)
                        location = new MapLocation(_selectedAD_Window_ID, info.C_Location, _mappingDetail.ExcelCol, _mappingDetail.TabDetail[_selected_table_index]);

                    //if (LocationMappedCols != null)
                    //    location.MappedColumns = LocationMappedCols[_selected_table_index];
                    //if (LocationExcelCols != null)
                    //    location.ExcelColIndex = LocationExcelCols[_selected_table_index];
                    //if (LocationMapText != null)
                    //    location.MappedList = LocationMapText[_selected_table_index];
                    //if (LocationMappedColsName != null)
                    //    location.MappedColumnName = LocationMappedColsName[_selected_table_index];


                    location.Closed += (ov, ev) =>
                        {
                            //LocationMappedCols[_selected_table_index] = location.MappedColumns;
                            //LocationExcelCols[_selected_table_index] = location.ExcelColIndex;
                            //LocationMapText[_selected_table_index] = location.MappedList;
                            //LocationMappedColsName[_selected_table_index] = location.MappedColumnName;
                            //IsLocation = false;
                        };

                    location.Show();
                }
                else
                {
                    infoWin.PopupMenuItem(2).IsEnabled = false;
                    //IsLocation = false;
                    if (field.IsParentColumn())
                    {
                        SetControl(false, null);
                        return;
                    }

                    SetControl(false, field);

                    //Find Default value from dicitionary

                    Dictionary<String, Object> temp = DefaultValues[cmbTable.SelectedIndex];
                    Object value = temp[SelectedColumn];

                    //if (value != null)
                    //{
                    //    if (value.ToString().Length > 0)
                    //        lblDefaultHeader.Visibility = System.Windows.Visibility.Visible;
                    //    else
                    //        lblDefaultHeader.Visibility = System.Windows.Visibility.Collapsed;

                    //    lblDefaultValue.Text = value.ToString();
                    //}
                    //else
                    //{
                    //    lblDefaultHeader.Visibility = System.Windows.Visibility.Collapsed;
                    //    lblDefaultValue.Text = String.Empty;
                    //}
                }
            }
        }


        /// <summary>
        /// Gives the field object of given column name
        /// </summary>
        /// <param name="columnName">column name</param>
        /// <returns>field object</returns>
        private GridField GetTargetMField(string columnName)
        {
            // if no column name, then return null
            if (columnName == null)
                return null;
            // else find field for the given column
            for (int c = 0; c < _findFields.Length; c++)
            {
                GridField field = _findFields[c];
                if (columnName.Equals(field.GetColumnName()))
                    return field;
            }
            return null;
        }

        private void lstDBCols_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            OnColumnChanged();
        }


        Control DynamicDefaultControl = null;
        /// <summary>
        /// Sets a control for value1 or value2
        /// </summary>
        /// <param name="isValue1">true if set control at value1 position else false</param>
        /// <param name="field">field object</param>
        private void SetControl(bool isValue1, GridField field)
        {
            // set column and row position
            int col = 0;
            int row = 0;

            /*****Get control form specified column and row from Grid***********/
            var eList = from child in defaultValueGrid.Children
                        where Grid.GetRow((FrameworkElement)child) == row && Grid.GetColumn((FrameworkElement)child) == col
                        select child;

            //Remove any elements in the list
            if (eList.Count() > 0)
            {
                Control c = (Control)eList.First();


                defaultValueGrid.Children.Remove(c);

                Grid.SetColumn(c, 111);
                Grid.SetRow(c, 111);
                if (c is IControl)
                {
                    //((IControl)c).Dispose();
                }
                c = null;
                DynamicDefaultControl = c;
                if (field == null)
                {
                    btnSetDefault.Visibility = System.Windows.Visibility.Collapsed;
                    //lblDefaultHeader.Visibility = System.Windows.Visibility.Collapsed;
                    //lblDefaultValue.Text = String.Empty;
                    return;
                }
            }
            /**********************************/

            /*
             * Disable Default Value Option disabled for primary keys
             * */

            if (field == null)
            {
                //lblDefaultHeader.Visibility = System.Windows.Visibility.Collapsed;
                //lblDefaultValue.Text = String.Empty;
                return;
            }

            bool proceed = true;

            if (SelectedColumn.StartsWith("AD_Client_ID", StringComparison.OrdinalIgnoreCase))
                proceed = false;

            if (SelectedColumn.StartsWith("AD_Org_ID", StringComparison.OrdinalIgnoreCase))
                proceed = false;

            if (SelectedColumn.StartsWith(info.AllTables[_selected_table_index].TableName + "_ID", StringComparison.OrdinalIgnoreCase))
                proceed = false;

            if (proceed)
            {
                IControl crt = null;
                // if any filed is given
                if (field != null)
                {
                    // if field id any key, then show number textbox 
                    if (field.IsKey())
                    {
                        crt = new VAdvantage.Controls.VNumTextBox(field.GetColumnName(), false, false, true,
                                        DisplayType.Integer, field.GetColumnName());
                    }
                    else
                    {
                        crt = VControlFactory.GetControl(null, field, true, true, false);
                    }
                }
                else
                {
                    // if no field is given show an empty disabled textbox
                    crt = new VAdvantage.Controls.VTextBox("columnName", false, true, false, 20, 20, "format",
                              "GetObscureType", VAdvantage.Controls.VTextBox.TextType.Text, DisplayType.String);
                }
                if (crt != null)
                {
                    //crt.SetIsMandatory(false);
                    crt.SetReadOnly(false);
                    if (crt is Control)
                    {
                        // crt.SetReadOnly(false);
                        ((Control)crt).IsEnabled = true;
                    }
                    //set the control's width
                    Control c = (Control)crt;

                    c.IsEnabled = true;
                    btnSetDefault.IsEnabled = true;

                    if (crt.GetDisplayType() != DisplayType.YesNo)
                    {
                        //c.Width = 132;
                        c.Height = 23;
                    }
                    else
                    {
                        // c.Width = 15;
                        c.Height = 23;
                    }

                    if (crt.GetDisplayType() == DisplayType.Button)
                    {
                        c.IsEnabled = false;
                        btnSetDefault.IsEnabled = false;
                    }

                    Grid.SetRow(c, row);
                    Grid.SetColumn(c, col);

                    c.Margin = new Thickness(2);

                    this.defaultValueGrid.Children.Add(c);
                    btnSetDefault.Visibility = System.Windows.Visibility.Visible;
                    DynamicDefaultControl = c;
                }
            }
            else
            {
                //lblDefaultHeader.Visibility = System.Windows.Visibility.Collapsed;
                //lblDefaultValue.Text = String.Empty;
            }
        }


        List<List<int?>> StoredDefaultValueList = new List<List<int?>>();

        private void btnSetDefault_Click(object sender, RoutedEventArgs e)
        {
            if (DynamicDefaultControl == null)
            {
                ShowMessage.Error("SelectDBColumn", true);
                return;
            }

            Object value = (DynamicDefaultControl as IControl).GetValue();
            String text = (DynamicDefaultControl as IControl).GetText();

            String oldStoredValue = DefaultValues[cmbTable.SelectedIndex].ElementAt(lstDBCols.SelectedIndex).Key + " <===> " + DefaultValues[cmbTable.SelectedIndex].ElementAt(lstDBCols.SelectedIndex).Value;
            Dictionary<String, Object> temp = DefaultValues[cmbTable.SelectedIndex];
            temp[SelectedColumn] = text;
            DefaultValuesActual[cmbTable.SelectedIndex][SelectedColumn] = value;

            //  String storedDefaultValue = DefaultValues[cmbTable.SelectedIndex].ElementAt(lstDBCols.SelectedIndex).Key + " <===> " + text;
            String storedDefaultValue = info.AllTables[cmbTable.SelectedIndex].ColumnDetail[lstDBCols.SelectedIndex].AD_ColumnField_Name + " <===> " + value;
            int columnID = info.AllTables[_selected_table_index].ColumnDetail[lstDBCols.SelectedIndex].AD_Column_ID;

            int findIndex = StoredDefaultValueList[cmbTable.SelectedIndex].IndexOf(lstDBCols.SelectedIndex);
            if (!StoredDefaultValueList[cmbTable.SelectedIndex].Contains(lstDBCols.SelectedIndex))
                StoredDefaultValueList[cmbTable.SelectedIndex].Add(lstDBCols.SelectedIndex);
            else
                StoredDefaultValueList[cmbTable.SelectedIndex][findIndex] = lstDBCols.SelectedIndex;


            // string header = (lstDBCols.SelectedItem as KeyNamePair).GetName() + "<===>" + lstExcelCols.SelectedItem.ToString();
            //string colname = info.AllTables[cmbTable.SelectedIndex].ColumnDetail[lstDBCols.SelectedIndex].AD_Column_Name;
            int colID = info.AllTables[cmbTable.SelectedIndex].ColumnDetail[lstDBCols.SelectedIndex].AD_Column_ID;
            // string key = (lstDBCols.SelectedItem as KeyNamePair).GetKey().ToString() + cmbTable.SelectedIndex.ToString();
            string key = colID.ToString() + cmbTable.SelectedIndex.ToString();


            ExcelMappingDetail excel = new ExcelMappingDetail()
            {
                AD_Column_ID = colID,
                AD_Column_Name = info.AllTables[_selected_table_index].ColumnDetail[lstDBCols.SelectedIndex].AD_Column_Name,
                // AD_ColumnField_Name = (lstDBCols.SelectedItem as KeyNamePair).GetName(),
                AD_ColumnField_Name = (lstDBCols.SelectedItem as TextBlock).Text,
                //AD_Table_ID = (cmbTable.SelectedItem as KeyNamePair).GetKey(),
                //ExcelColIndex = lstExcelCols.SelectedIndex,
                ExcelColName = value.ToString(),
                //UniqueKey = lstDBCols.SelectedIndex,
                //IsIdentifier = false,
                PrimaryKey = 0
            };

            if (SavedDefaultValues == null)
                SavedDefaultValues = new ExcelMapping();

            if (SavedDefaultValues.TabDetail == null)
                SavedDefaultValues.TabDetail = _tabDetail;

            ExcelMappingPerTabDetail tabdetail = SavedDefaultValues.TabDetail[_selected_table_index];
            if (tabdetail == null)
            {
                tabdetail = new ExcelMappingPerTabDetail();
                tabdetail.AD_Table_ID = (cmbTable.SelectedItem as KeyNamePair).GetKey();
            }

            if (tabdetail.ColumnDetail == null)
                tabdetail.ColumnDetail = new List<ExcelMappingDetail>();
            bool chk = true;
            for (int i = 0; i < tabdetail.ColumnDetail.Count; i++)
            {
                if (excel.AD_Column_ID == tabdetail.ColumnDetail[i].AD_Column_ID)
                {
                    //  tabdetail.ColumnDetail.RemoveAt(i);
                    //tabdetail.ColumnDetail[i].ExcelColName = text;
                    tabdetail.ColumnDetail[i].ExcelColName = value.ToString();
                    tabdetail.ColumnDetail[i].ToDelete = false;
                    chk = false;
                    break;
                }
            }
            if (chk)
            {
                tabdetail.ColumnDetail.Add(excel);
            }
            //tabdetail.AD_Tab_ID = tab.GetAD_Tab_ID();
            tabdetail.AD_Tab_ID = tabdetail.AD_Tab_ID;

            //_mappingDetail.AD_Window_ID = _selectedAD_Window_ID;
            //_mappingDetail.CtxDic = dic;
            SavedDefaultValues.TabDetail[_selected_table_index] = tabdetail;

            SetDBColDefaultState();
            VTreeNode node = new VTreeNode("role.png", storedDefaultValue, int.Parse(key));
            //try
            //{
            node.ColName = DefaultValues[cmbTable.SelectedIndex].ElementAt(lstDBCols.SelectedIndex).Key + cmbTable.SelectedIndex.ToString();
            //    ((localDefulttreeView.Items[0] as VTreeNode).Items[cmbTable.SelectedIndex] as VTreeNode).Items.Add(node);
            //    //  removeDefault.AddTrigger(TriggerTypes.RightClick, node);
            //}
            //catch
            //{
            //ShowMessage.Error("DefaultValueAlreadySet", false);
            //return;
            int count = ((localDefulttreeView.Items[0] as VTreeNode).Items[cmbTable.SelectedIndex] as VTreeNode).Items.Count;
            for (int i = 0; i < count; i++)
            {
                if (node.ColName == (((localDefulttreeView.Items[0] as VTreeNode).Items[cmbTable.SelectedIndex] as VTreeNode).Items[i] as VTreeNode).ColName)
                {
                    ((localDefulttreeView.Items[0] as VTreeNode).Items[cmbTable.SelectedIndex] as VTreeNode).Items.Remove(((localDefulttreeView.Items[0] as VTreeNode).Items[cmbTable.SelectedIndex] as VTreeNode).Items[i]);
                    break;
                }
            }
            // ((localDefulttreeView.SelectedItem as VTreeNode).Parent as VTreeNode).Items.Remove(node);
            ((localDefulttreeView.Items[0] as VTreeNode).Items[cmbTable.SelectedIndex] as VTreeNode).Items.Add(node);


            //}
            removeDefault.AddTrigger(TriggerTypes.RightClick, node);
            //VTreeNode node = new VTreeNode("role.png", storedDefaultValue);
            //if (localDefulttreeView.Items.Contains(node))
            //{
            // //   lstDefaultValues.Items.Remove(oldStoredValue);
            //   // ((node).Parent as VTreeNode).Items.Remove(localDefulttreeView.SelectedItem);
            //    return;

            //}

            //if (value != null)
            //{
            //    //lstDefaultValues.Items.Add(storedDefaultValue);
            //    ((localDefulttreeView.Items[0] as VTreeNode).Items[cmbTable.SelectedIndex] as VTreeNode).Items.Add(node);
            //}

            ImportDetailTab.SelectedIndex = 1;  //show default tab
        }

        private void lstDBCols_SelectionChanged_1(object sender, SelectionChangedEventArgs e)
        {
            OnColumnChanged();
        }


        public static byte[] ReadFully(Stream input)
        {
            byte[] buffer = new byte[25 * 1024 * 1024];

            if (input.Length > buffer.Length)
            {
                return null;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                int read;
                int offset = 0;
                while ((read = input.Read(buffer, offset, buffer.Length)) > 0)
                {
                    ms.Write(buffer, 0, read);
                }
                return ms.ToArray();
            }
        }


        System.IO.Stream fileStream = null;
        byte[] fileArray = null;
        string FileName = "";

        private void btnLoadFile_Click(object sender, string e)
        {
            try
            {
                OpenFileDialog dialog = new OpenFileDialog();
                dialog.Filter = "Excel 11 (*.xlsx)|*.xlsx";
                if ((bool)dialog.ShowDialog())
                {
                    fileStream = dialog.File.OpenRead();
                    fileArray = ReadFully(fileStream);

                    fileStream.Close();
                    fileStream.Dispose();

                    FileName = dialog.File.Name;
                    //tbFileName.Text = Msg.GetMsg("ExcelFileLoaded");
                    //btnConnect.IsEnabled = true;
                    //imgFileStatus.Source = Envs.LoadImageSource("Ok16.png");

                    // btnUploadFile.IsEnabled = true;

                    UploadFile();
                    LoadDefaultTree(true);


                }
                else
                {
                    //btnUploadFile.IsEnabled = false;
                }
            }
            catch
            {
                ShowMessage.Info("Can'tOpenFile", false, "", null);
            }
        }

        String NewFileName = "";

        public void LoadDefaultTree(bool refreshTree)
        {
            var client = AEnv.GetExcelImportService();
            try
            {
                loadDefultColumns.IsBusy = true;
                client.LoadDefaultValuesCompleted += (se, ee) =>
                {
                    client.CloseAsync();
                    loadDefultColumns.IsBusy = false;
                    if (ee.Result != null)
                    {
                        SavedDefaultValues = ee.Result;
                    }
                    if (SavedDefaultValues != null && refreshTree)
                    {

                        for (int b = 0; b < SavedDefaultValues.TabDetail.Count; b++)
                        {
                            if (SavedDefaultValues.TabDetail[b].ColumnDetail != null)
                            {
                                for (int c = 0; c < SavedDefaultValues.TabDetail[b].ColumnDetail.Count; c++)
                                {
                                    for (int j = 0; j < info.AllTables[b].ColumnDetail.Count; j++)
                                    {
                                        if (SavedDefaultValues.TabDetail[b].ColumnDetail[c].AD_Column_ID == info.AllTables[b].ColumnDetail[j].AD_Column_ID)
                                        {
                                            String colName = info.AllTables[b].ColumnDetail[j].AD_ColumnField_Name;

                                            // String colName = SavedDefaultValues.TabDetail[b].ColumnDetail[c].AD_Column_Name;
                                            String excelName = SavedDefaultValues.TabDetail[b].ColumnDetail[c].ExcelColName;
                                            int PrimaryKey = Convert.ToInt32((SavedDefaultValues.TabDetail[b].ColumnDetail[c].AD_Column_ID.ToString() + b.ToString()));
                                            //m_MappedColumns.Add(_mappingDetail.TabDetail[b].ColumnDetail[c].AD_Column_ID);
                                            //Dictionary<string,object> _dic= new Dictionary<string,object>();
                                            //_dic.Keys=colName;
                                            //_dic.Values
                                            //DefaultValuesActual.Add(new Dictionary<string,object>(
                                            VTreeNode node = new VTreeNode("role.png", colName + "<===>" + excelName, PrimaryKey);
                                            node.ColName = SavedDefaultValues.TabDetail[b].ColumnDetail[c].AD_Column_Name + b.ToString();

                                            int count = ((localDefulttreeView.Items[0] as VTreeNode).Items[b] as VTreeNode).Items.Count;
                                            for (int i = 0; i < count; i++)
                                            {
                                                if (node.ColName == (((localDefulttreeView.Items[0] as VTreeNode).Items[b] as VTreeNode).Items[i] as VTreeNode).ColName)
                                                {
                                                    ((localDefulttreeView.Items[0] as VTreeNode).Items[b] as VTreeNode).Items.Remove(((localDefulttreeView.Items[0] as VTreeNode).Items[b] as VTreeNode).Items[i]);
                                                    break;
                                                }
                                            }
                                            // ((localDefulttreeView.SelectedItem as VTreeNode).Parent as VTreeNode).Items.Remove(node);
                                            ((localDefulttreeView.Items[0] as VTreeNode).Items[b] as VTreeNode).Items.Add(node);




                                            removeDefault.AddTrigger(TriggerTypes.RightClick, node);
                                            //DefaultValuesActual[b].Add(colName, excelName);

                                            DefaultValuesActual[b][info.AllTables[b].ColumnDetail[j].AD_Column_Name] = excelName;
                                        }
                                    }
                                    m_MapIndex++;
                                }
                            }
                        }
                        SetDBColDefaultState();
                    }

                };
                client.LoadDefaultValuesAsync(SavedDefaultValues);
            }
            catch
            {

            }
        }

        public void UploadFile()
        {
            try
            {
                if (fileArray == null)
                    return;

                lstExcelCols.Items.Clear();

                SetBusy(true, loadExcelColumns);
                string extension = FileName.Substring(FileName.LastIndexOf(".") + 1);

                if (extension.Equals("xls", StringComparison.OrdinalIgnoreCase))
                {
                    ShowMessage.Error("OldFormatnotSupported", false);
                    return;
                }

                Guid g = Guid.NewGuid();
                NewFileName = g.ToString() + "." + extension;

                bool pass = false;

                Dictionary<string, string> dic = (Dictionary<String, string>)Envs.GetCtx().GetMap();
                //_IsFetchingExcelColumn = true;


                bool actualImportState = btnImport.IsEnabled;
                btnImport.IsEnabled = false;
                //btnUploadFile.IsEnabled = false;
                BackgroundWorker bgw = new BackgroundWorker();

                var client = AEnv.GetExcelImportService();
                bgw.DoWork += (ov, ev) =>
                {


                    client.UploadExcelCompleted += (c, d) =>
                    {
                        if (d.Error == null)
                        {
                            if (d.Result != null)
                                _mappingDetail = d.Result;
                        }
                        pass = true;
                    };
                    bool chk = false;
                    _mappingDetail.FileName = NewFileName;

                    client.UploadExcelAsync(fileArray, NewFileName, _mappingDetail);


                    while (!pass)
                    {
                        Thread.Sleep(0);
                    }
                };

                bgw.RunWorkerCompleted += (ov, ev) =>
                {
                    client.CloseAsync();


                    List<string> lostColumns = new List<string>();
                    if (_mappingDetail != null)
                    {

                        if (_mappingDetail.ExcelCol == null)
                        {
                            return;
                        }
                        if (_mappingDetail.ExcelCol.Count <= 0)
                        {
                            ShowMessage.Error("MappedColNotFound", false);

                            //btnUploadFile.IsEnabled = false;
                            SetBusy(false, loadExcelColumns);

                            btnLoadFile.IsEnabled = true;
                            btnImport.IsEnabled = false;
                            return;
                        }

                        if (_mappingDetail.ExcelCol != null)
                        {
                            foreach (String s in _mappingDetail.ExcelCol)
                            {
                                lstExcelCols.Items.Add(s);
                            }
                        }


                        for (int b = 0; b < _mappingDetail.TabDetail.Count; b++)
                        {
                            if (_mappingDetail.TabDetail[b].ColumnDetail != null)
                            {
                                for (int c = 0; c < _mappingDetail.TabDetail[b].ColumnDetail.Count; c++)
                                {
                                    for (int j = 0; j < info.AllTables[b].ColumnDetail.Count; j++)
                                    {
                                        if (_mappingDetail.TabDetail[b].ColumnDetail[c].AD_Column_ID == info.AllTables[b].ColumnDetail[j].AD_Column_ID)
                                        {
                                            String colName = info.AllTables[b].ColumnDetail[j].AD_ColumnField_Name;
                                            String excelName = _mappingDetail.TabDetail[b].ColumnDetail[c].ExcelColName;
                                            int PrimaryKey = _mappingDetail.TabDetail[b].ColumnDetail[c].PrimaryKey;
                                            m_MappedColumns.Add(_mappingDetail.TabDetail[b].ColumnDetail[c].AD_Column_ID);
                                            VTreeNode node = new VTreeNode("role.png", colName + "<===>" + excelName, PrimaryKey);
                                            ((localtreeView.Items[0] as VTreeNode).Items[b] as VTreeNode).Items.Add(node);
                                            removeMapping.AddTrigger(TriggerTypes.RightClick, node);
                                            if (_mappingDetail.TabDetail[b].ColumnDetail[c].ToDelete)
                                            {
                                                lostColumns.Add(_mappingDetail.TabDetail[b].ColumnDetail[c].ExcelColName);
                                                _mappingDetail.TabDetail[b].ColumnDetail[c].ToDelete = false;
                                            }


                                            break;
                                        }

                                    }

                                    if (b == 0)
                                    {
                                        if (_mappingDetail.TabDetail[0].ColumnDetail[c].IsIdentifier)
                                            tbIdentifierValue.Text = IDENTIFIER_STRING + _mappingDetail.TabDetail[0].ColumnDetail[c].AD_Column_Name;
                                    }



                                    //if (_mappingDetail.TabDetail[b].LocationMapping != null)
                                    //{
                                    //    List<int> excelIndex = new List<int>();
                                    //    List<string> MappingList = new List<string>();
                                    //    List<string> mappedColName = new List<string>();
                                    //    for (int d = 0; d < _mappingDetail.TabDetail[b].LocationMapping.Count; d++)
                                    //    {
                                    //        excelIndex.Add(_mappingDetail.TabDetail[b].LocationMapping[d].ExcelColIndex);
                                    //        MappingList.Add(_mappingDetail.TabDetail[b].LocationMapping[d].AD_Column_Name+"<===>"+
                                    //            _mappingDetail.TabDetail[b].LocationMapping[d].ExcelColName);
                                    //        mappedColName.Add(_mappingDetail.TabDetail[b].LocationMapping[d].AD_Column_Name);
                                    //    }
                                    //}


                                    m_MapIndex++;
                                }

                            }
                        }
                        SetDBColMappedState();
                        LoadMappedLocationInfo();
                        StringBuilder errorMsg = new StringBuilder();
                        for (int i = 0; i < lostColumns.Count; i++)
                        {
                            errorMsg.Append("\n\r ");
                            errorMsg.Append("'" + lostColumns[i] + "'");
                        }
                        if (errorMsg.Length > 0)
                        {
                            btnImport.IsEnabled = false;
                            errorMsg.Append(" Columns Not found in ExcelFile but found in Mapping. Please Remove From Mapping OR Add column in Excel and Reload File");
                            ShowMessage.Error("MappedColNotFound", false, errorMsg.ToString());
                        }
                        else
                        {
                            btnImport.IsEnabled = true;
                        }

                        //LoadSavedDetail();

                        //  btnUploadFile.IsEnabled = false;




                        //SetDefaultValue in Tree

                        SetBusy(false, loadExcelColumns);

                        btnLoadFile.IsEnabled = false;

                    }
                    else
                    {
                        ShowMessage.Error("ErrorCloseAndOpen", false);
                        btnNew.IsEnabled = false;
                        cmbMapping.IsEnabled = false;
                        txtSearchWindow.IsEnabled = false;
                        lstSearchedWindows.IsEnabled = false;
                        btnMap.IsEnabled = false;
                        //     btnRemoveMap.IsEnabled = false;
                        return;
                    }
                    //_IsFetchingExcelColumn = false;
                };

                bgw.RunWorkerAsync();
            }
            catch (Exception ex)
            {
                ShowMessage.Error(ex.Message, false);
                return;
            }
        }


        void SetDBColDefaultState()
        {
            int b = cmbTable.SelectedIndex;
            if (b == -1)
            {
                return;
            }
            if (SavedDefaultValues.TabDetail[b].ColumnDetail == null)
            {
                return;
            }

            for (int c = 0; c < SavedDefaultValues.TabDetail[b].ColumnDetail.Count; c++)
            {
                for (int d = 0; d < lstDBCols.Items.Count; d++)
                {
                    if ((lstDBCols.Items[d] as TextBlock).Text.Contains("(" + SavedDefaultValues.TabDetail[b].ColumnDetail[c].AD_Column_Name + ")"))
                    {
                        if (SavedDefaultValues.TabDetail[b].ColumnDetail[c].ToDelete)
                        {
                            (lstDBCols.Items[d] as TextBlock).Foreground = new SolidColorBrush(Colors.Black);
                            (lstDBCols.Items[d] as TextBlock).FontWeight = FontWeights.Normal;
                        }
                        else
                        {
                            (lstDBCols.Items[d] as TextBlock).Foreground = new SolidColorBrush(Color.FromArgb(255, 0, 145, 255));
                            (lstDBCols.Items[d] as TextBlock).FontWeight = FontWeights.Bold;
                        }
                        break;

                    }
                }
            }
        }
        void SetDBColMappedState()
        {

            int b = cmbTable.SelectedIndex;
            if (b == -1)
            {
                return;
            }
            if (_mappingDetail.TabDetail[b].ColumnDetail == null)
            {
                return;
            }

            for (int c = 0; c < _mappingDetail.TabDetail[b].ColumnDetail.Count; c++)
            {
                for (int d = 0; d < lstDBCols.Items.Count; d++)
                {
                    if ((lstDBCols.Items[d] as TextBlock).Text.Contains("(" + _mappingDetail.TabDetail[b].ColumnDetail[c].AD_Column_Name + ")"))
                    {
                        if (_mappingDetail.TabDetail[b].ColumnDetail[c].ToDelete)
                        {
                            (lstDBCols.Items[d] as TextBlock).Foreground = new SolidColorBrush(Colors.Black);
                            (lstDBCols.Items[d] as TextBlock).FontWeight = FontWeights.Normal;
                        }
                        else
                        {
                            (lstDBCols.Items[d] as TextBlock).Foreground = new SolidColorBrush(Colors.Green);
                            (lstDBCols.Items[d] as TextBlock).FontWeight = FontWeights.Bold;
                        }
                        break;

                    }
                }
            }


        }


        void LoadMappedLocationInfo()
        {
            List<int> excelIndex = new List<int>();
            List<string> MappingList = new List<string>();
            List<string> mappedColName = new List<string>();
            for (int b = 0; b < _mappingDetail.TabDetail.Count; b++)
            {

                if (_mappingDetail.TabDetail[b].LocationMapping != null)
                {

                    for (int d = 0; d < _mappingDetail.TabDetail[b].LocationMapping.Count; d++)
                    {
                        excelIndex.Add(_mappingDetail.TabDetail[b].LocationMapping[d].ExcelColIndex);
                        MappingList.Add(_mappingDetail.TabDetail[b].LocationMapping[d].AD_Column_Name + "<===>" +
                            _mappingDetail.TabDetail[b].LocationMapping[d].ExcelColName);
                        mappedColName.Add(_mappingDetail.TabDetail[b].LocationMapping[d].AD_Column_Name);
                    }
                    if (location == null)
                    {
                        location = new MapLocation(_selectedAD_Window_ID, info.C_Location, _mappingDetail.ExcelCol, _mappingDetail.TabDetail[b]);
                    }
                }
            }

            if (location != null)
            {
                location.ExcelColIndex = excelIndex;
                location.MappedList = MappingList;
                location.MappedColumnName = mappedColName;
            }
        }

        //private void btnUploadFile_Click(object sender, string  e)
        //{
        //    try
        //    {
        //        if (fileArray == null)
        //            return;

        //        lstExcelCols.Items.Clear();

        //        SetBusy(true, loadExcelColumns);
        //        string extension = FileName.Substring(FileName.LastIndexOf(".") + 1);

        //        if (extension.Equals("xls", StringComparison.OrdinalIgnoreCase))
        //        {
        //            ShowMessage.Error("OldExcelFormatNotSupportedYet", false);
        //            return;
        //        }

        //        Guid g = Guid.NewGuid();
        //        NewFileName = g.ToString() + "." + extension;

        //        bool pass = false;

        //        Dictionary<string, string> dic = (Dictionary<String, string>)Envs.GetCtx().GetMap();
        //        //_IsFetchingExcelColumn = true;


        //        bool actualImportState = btnImport.IsEnabled;
        //        btnImport.IsEnabled = false;
        //        btnUploadFile.IsEnabled = false;
        //        BackgroundWorker bgw = new BackgroundWorker();

        //        var client = AEnv.GetExcelImportService();
        //        bgw.DoWork += (ov, ev) =>
        //            {

        //                client.UploadExcelCompleted += (c, d) =>
        //                    {
        //                        if (d.Error == null)
        //                        {
        //                            if(d.Result != null)
        //                                _mappingDetail = d.Result;
        //                        }
        //                        pass = true;
        //                    };

        //                _mappingDetail.FileName = NewFileName;

        //                client.UploadExcelAsync(fileArray, NewFileName, _mappingDetail );


        //                while (!pass)
        //                    Thread.Sleep(0);
        //            };

        //        bgw.RunWorkerCompleted += (ov, ev) =>
        //            {
        //                client.CloseAsync();

        //                if (_mappingDetail != null)
        //                {

        //                    if (_mappingDetail.ExcelCol.Count <= 0)
        //                    {
        //                        ShowMessage.Error("MappedColumnsNotFound", false);

        //                        btnUploadFile.IsEnabled = false;
        //                        SetBusy(false, loadExcelColumns);

        //                        btnLoadFile.IsEnabled = true;
        //                        btnImport.IsEnabled = false;
        //                        return;
        //                    }

        //                    if (_mappingDetail.ExcelCol != null)
        //                    {
        //                        foreach (String s in _mappingDetail.ExcelCol)
        //                        {
        //                            lstExcelCols.Items.Add(s);
        //                        }
        //                    }


        //                    for (int b = 0; b < _mappingDetail.TabDetail.Count; b++)
        //                    {
        //                        if (_mappingDetail.TabDetail[b].ColumnDetail != null)
        //                        {
        //                            for (int c = 0; c < _mappingDetail.TabDetail[b].ColumnDetail.Count; c++)
        //                            {
        //                                String colName = _mappingDetail.TabDetail[b].ColumnDetail[c].AD_Column_Name;
        //                                String excelName = _mappingDetail.TabDetail[b].ColumnDetail[c].ExcelColName;
        //                                int PrimaryKey = _mappingDetail.TabDetail[b].ColumnDetail[c].PrimaryKey;
        //                                m_MappedColumns.Add(_mappingDetail.TabDetail[b].ColumnDetail[c].AD_Column_ID);
        //                                ((localtreeView.Items[0] as VTreeNode).Items[b] as VTreeNode).Items.Add(new VTreeNode("role.png", colName + "<===>" + excelName, PrimaryKey));                                       


        //                                m_MapIndex++;
        //                            }
        //                        }
        //                    }
        //                    //LoadSavedDetail();

        //                    btnUploadFile.IsEnabled = false;
        //                    SetBusy(false, loadExcelColumns);

        //                    btnLoadFile.IsEnabled = false;
        //                    btnImport.IsEnabled = true;
        //                }
        //                else
        //                {
        //                    ShowMessage.Error("Error! Close the window and open again", false);
        //                    btnNew.IsEnabled = false;
        //                    cmbMapping.IsEnabled = false;
        //                    txtSearchWindow.IsEnabled = false;
        //                    lstSearchedWindows.IsEnabled = false;
        //                    btnMap.IsEnabled = false;
        //                    btnRemoveMap.IsEnabled = false;
        //                    return;
        //                }
        //                //_IsFetchingExcelColumn = false;
        //            };

        //        bgw.RunWorkerAsync();
        //    }
        //    catch(Exception ex)
        //    {
        //        ShowMessage.Error(ex.Message, false);
        //        return;
        //    }
        //}

        private void lstExcelCols_MouseRightButtonDown(object sender, MouseButtonEventArgs e)
        {
            e.Handled = true;
        }

        private void lstExcelCols_MouseRightButtonUp(object sender, MouseButtonEventArgs e)
        {
            try
            {
                VisualTreeHelper.FindElementsInHostCoordinates(e.GetPosition(null), (sender as ListBox)).OfType<ListBoxItem>().First().IsSelected = true;
            }
            catch { }
        }

        private void btnMap_Click(object sender, string e)
        {

            //var values = (from value in info.WindowList.Values orderby value select value).ToList();
            string tableName = info.AllTables[_selected_table_index].TableName;
            if (tableName.Contains("(") && tableName.Contains(")"))
            {
                tableName = tableName.Substring(tableName.IndexOf("(") + 1);
                if (tableName.Contains(")"))
                {
                    tableName = tableName.Replace(")", "");
                    tableName = tableName.Trim();
                }
            }
            //tableName = tableName.Substring(0, tableName.IndexOf(" ("));
            try
            {
                if (lstDBCols.SelectedIndex < 0)
                {
                    ShowMessage.Info("SelectDBColumn", true, "", "");
                    return;
                }
                else if (lstExcelCols.SelectedIndex < 0)
                {
                    ShowMessage.Info("SelectExcelColumn", true, "", "");
                    return;
                }
                //if ((lstDBCols.SelectedItem as KeyNamePair).Name.StartsWith("AD_Client_ID", StringComparison.OrdinalIgnoreCase))
                //{
                //    ShowMessage.Error("CannotMapClientID", false);
                //    return;
                //}
                //else if ((lstDBCols.SelectedItem as KeyNamePair).Name.StartsWith("AD_Org_ID", StringComparison.OrdinalIgnoreCase))
                //{
                //    ShowMessage.Error("CannotMapOrgID", false);
                //    return;
                //}
                //else if ((lstDBCols.SelectedItem as KeyNamePair).Name.StartsWith(tableName + "_ID", StringComparison.OrdinalIgnoreCase))
                //{
                //    ShowMessage.Error("CannotMapPrimaryKey", false);
                //    return;
                //}
                //else if (m_MappedColumns.Contains((lstDBCols.SelectedItem as KeyNamePair).GetKey()))
                //{
                //    ShowMessage.Error("AlreadyMapped", false);
                //    return;
                //}
                string colname = info.AllTables[cmbTable.SelectedIndex].ColumnDetail[lstDBCols.SelectedIndex].AD_Column_Name;
                string displayName = info.AllTables[cmbTable.SelectedIndex].ColumnDetail[lstDBCols.SelectedIndex].AD_ColumnField_Name;
                int colID = info.AllTables[cmbTable.SelectedIndex].ColumnDetail[lstDBCols.SelectedIndex].AD_Column_ID;
                if (colname.StartsWith("AD_Client_ID", StringComparison.OrdinalIgnoreCase))
                {
                    ShowMessage.Error("CannotMapClientID", false);
                    return;
                }
                else if (colname.StartsWith("AD_Org_ID", StringComparison.OrdinalIgnoreCase))
                {
                    ShowMessage.Error("CannotMapOrgID", false);
                    return;
                }
                //else if (colname.StartsWith(tableName + "_ID", StringComparison.OrdinalIgnoreCase))
                //{
                //    ShowMessage.Error("CannotMapPrimaryKey", false);
                //    return;
                //}
                //else if ((tableName+"_ID").Contains(colname))
                //{
                //    ShowMessage.Error("CannotMapPrimaryKey", false);
                //    return;                
                //}
                else if (colname.ToUpper().Equals((tableName + "_ID").ToUpper()))
                {
                    ShowMessage.Error("CannotMapID", false);
                    return;
                }
                else if (m_MappedColumns.Contains(colID))
                {
                    ShowMessage.Error("AlreadyMapped", false);
                    return;
                }



                /* New code */
                ExcelMappingDetail excel = new ExcelMappingDetail()
                {
                    // AD_Column_ID = (lstDBCols.SelectedItem as KeyNamePair).GetKey(),
                    AD_Column_ID = colID,
                    AD_Column_Name = info.AllTables[_selected_table_index].ColumnDetail[lstDBCols.SelectedIndex].AD_Column_Name,
                    //AD_ColumnField_Name = (lstDBCols.SelectedItem as KeyNamePair).GetName(),
                    AD_ColumnField_Name = (lstDBCols.SelectedItem as TextBlock).Text,
                    //AD_Table_ID = (cmbTable.SelectedItem as KeyNamePair).GetKey(),
                    ExcelColIndex = lstExcelCols.SelectedIndex,
                    ExcelColName = lstExcelCols.SelectedItem.ToString(),
                    UniqueKey = lstDBCols.SelectedIndex,
                    IsIdentifier = false,
                    PrimaryKey = 0
                };

                if (_mappingDetail == null)
                    _mappingDetail = new ExcelMapping();

                if (_mappingDetail.TabDetail == null)
                    _mappingDetail.TabDetail = _tabDetail;

                ExcelMappingPerTabDetail tabdetail = _mappingDetail.TabDetail[_selected_table_index];
                if (tabdetail == null)
                {
                    tabdetail = new ExcelMappingPerTabDetail();
                    tabdetail.AD_Table_ID = (cmbTable.SelectedItem as KeyNamePair).GetKey();
                }

                if (tabdetail.ColumnDetail == null)
                    tabdetail.ColumnDetail = new List<ExcelMappingDetail>();
                bool chk = true;
                for (int i = 0; i < tabdetail.ColumnDetail.Count; i++)
                {
                    if (excel.AD_Column_ID == tabdetail.ColumnDetail[i].AD_Column_ID)
                    {
                        //  tabdetail.ColumnDetail.RemoveAt(i);
                        // tabdetail.ColumnDetail[i].ExcelColName = text;
                        tabdetail.ColumnDetail[i].ToDelete = false;
                        chk = false;
                        break;
                    }
                }
                if (chk)
                {
                    tabdetail.ColumnDetail.Add(excel);
                }

                //  tabdetail.AD_Tab_ID = tab.GetAD_Tab_ID();
                tabdetail.AD_Tab_ID = tabdetail.AD_Tab_ID;

                //_mappingDetail.AD_Window_ID = _selectedAD_Window_ID;
                //_mappingDetail.CtxDic = dic;
                _mappingDetail.TabDetail[_selected_table_index] = tabdetail;

                //tabdetail[_selected_table_index] = mapping;
                /************New code finish**************/

                //  m_MappedColumns.Add((lstDBCols.SelectedItem as KeyNamePair).GetKey());
                m_MappedColumns.Add(colID);
                //m_MappedTables.Add((cmbTable.SelectedItem as KeyNamePair).GetKey());
                //m_ExcelColIndex.Add(lstExcelCols.SelectedIndex);
                //m_ExcelColName.Add(lstExcelCols.SelectedItem.ToString());
                //m_MapIndex++;
                // string header = (lstDBCols.SelectedItem as KeyNamePair).GetName() + "<===>" + lstExcelCols.SelectedItem.ToString();
                string header = displayName + "<===>" + lstExcelCols.SelectedItem.ToString();
                // VTreeNode node = new VTreeNode("role.png", header, (lstDBCols.SelectedItem as KeyNamePair).GetKey());
                VTreeNode node = new VTreeNode("role.png", header, colID);
                ((localtreeView.Items[0] as VTreeNode).Items[cmbTable.SelectedIndex] as VTreeNode).Items.Add(node);
                removeMapping.AddTrigger(TriggerTypes.RightClick, node);
                //localtreeView.ExpandAll();
                SetDBColMappedState();
                ImportDetailTab.SelectedIndex = 0;
            }
            catch (Exception ex)
            {
                ShowMessage.Error(ex.Message, false);
                return;
            }
        }

        int GetTable_ID(int AD_Tab_ID)
        {
            string sql = @" SELECT ad_table.ad_table_id
                                   FROM ad_table
                                INNER JOIN ad_tab
                                     ON( ad_table.AD_TABLE_ID = ad_tab.AD_TABLE_ID )
                                  WHERE ad_tab.ad_tab_id             =" + AD_Tab_ID;
            try
            {
                return Convert.ToInt32(DB.ExecuteScalar(sql));
            }
            catch
            {
                return 0;
            }
        }
        void IsImportProcessing(bool isDoing)
        {
            btnMap.IsEnabled = isDoing;
            //  btnUploadFile.IsEnabled = isDoing;
            btnSave.IsEnabled = isDoing;
            //  btnRemoveMap.IsEnabled = isDoing;
            lstDBCols.IsEnabled = isDoing;
            lstExcelCols.IsEnabled = isDoing;
            cmbTable.IsEnabled = isDoing;
            lstSearchedWindows.IsEnabled = isDoing;
            txtSearchWindow.IsEnabled = isDoing;
            btnImport.IsEnabled = isDoing;
            btnLoadFile.IsEnabled = isDoing;
            btnNew.IsEnabled = isDoing;
            btnCancelNew.IsEnabled = isDoing;
            cmbMapping.IsEnabled = isDoing;
            btnSaveAs.IsEnabled = isDoing;
        }

        //ImportResult impres = null;

        private void btnImport_Click(object sender, string e)
        {
            rightUpperGrid.BusyContent = Msg.GetMsg("Importing");
            StartImport(false);
        }


        Dictionary<string, string> dic = null;

        void StartImport(bool doSave)
        {
            if (lstDBCols.Items.Count <= 0)
                return;

            if (lstExcelCols.Items.Count <= 0)
                return;

            if (m_MappedColumns.Count <= 0)
            {
                ShowMessage.Error(Msg.GetMsg("NothingToImport"), false);
                return;
            }

            ImportDetailTab.SelectedIndex = 2;

            _import.Clear();
            _import.Add(new CallbackDetail()
            {
                Status = "Please wait...Import Started",
                Query = "",
                Error = ""
            });

            bool pass = false;
            BackgroundWorker bgw = new BackgroundWorker();

            SetBusy(true, rightUpperGrid);
            IsImportProcessing(false);

            if ((bool)chkCommit.IsChecked)
                _mappingDetail.DoCommit = true;
            else
                _mappingDetail.DoCommit = false;

            var client = AEnv.GetExcelImportService();

            bgw.DoWork += (ov, ev) =>
            {
                client.ImportNowCompleted += (a, b) =>
                    {
                        pass = true;
                    };

                client.QueryExecutedReceived += (a, b) =>
                    {
                        Dispatcher.BeginInvoke(delegate()
                        {
                            _import.Add(b.cbd);
                        });
                    };

                _mappingDetail.FileName = NewFileName;
                client.ImportNowAsync(_mappingDetail, DefaultValuesActual, info, SavedDefaultValues);

                while (!pass)
                    Thread.Sleep(0);
            };

            bgw.RunWorkerCompleted += (ov, ev) =>
            {
                btnExportLog.IsEnabled = true;
                client.CloseAsync();


                ShowMessage.Info("ImportDone", true, "", "");

                //IsImportDone = true;
                IsImportProcessing(true);
                SetBusy(false, rightUpperGrid);

            };

            bgw.RunWorkerAsync();
        }



        //void LoadSavedDetail()
        //{
        //    try
        //    {
        //        ImportClientInfo clientInfo = info.info;
        //        //get saved records in node
        //        for (int i = 0; i <= clientInfo.m_MappedColumns.Count - 1; i++)
        //        {
        //            m_MappedColumns.Add(clientInfo.m_MappedColumns[i]);
        //            m_MappedTables.Add(clientInfo.m_MappedTables[i]);
        //            m_ExcelColIndex.Add(clientInfo.m_ExcelColIndex[i]);
        //            m_ExcelColName.Add(clientInfo.m_ExcelColName[i]);

        //            var keys = (from key in info.WindowList.Keys select key).ToList();
        //            int tableIndex = keys.IndexOf(clientInfo.m_MappedTables[i]);
        //            Dictionary<int, string> dicinfo = info.WindowColumns[tableIndex];
        //            string colName = dicinfo[clientInfo.m_MappedColumns[i]];

        //            m_MapIndex++;
        //            ((localtreeView.Items[0] as VTreeNode).Items[tableIndex] as VTreeNode).Items.Add(new VTreeNode("role.png", colName + "<===>" + clientInfo.m_ExcelColName[i], m_MapIndex));
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        ShowMessage.Error(ex.Message, false);
        //        return;
        //    }
        //}


        FormFrame _frame;

        MapLocation location = null;

        bool SearchIdentifierIndex(ExcelMappingDetail map)
        {
            if (map.IsIdentifier)
                return true;
            else
                return false;
        }

        public void Init(int windowNo, FormFrame frame)
        {
        }

        private void btnSave_Click(object sender, string e)
        {
            if (lstDBCols.Items.Count <= 0)
                return;

            if (lstExcelCols.Items.Count <= 0)
                return;

            if (m_MappedColumns.Count <= 0)
            {
                ShowMessage.Error(Msg.GetMsg("NothingToImport"), false);
                return;
            }

            btnImport.IsEnabled = true;

            rightUpperGrid.BusyContent = Msg.GetMsg("Saving");
            SetBusy(true, rightUpperGrid);
            IsImportProcessing(false);

            BackgroundWorker bgw = new BackgroundWorker();
            var client = AEnv.GetExcelImportService();

            bgw.DoWork += (ov, ev) =>
                {
                    bool pass = false;

                    client.SaveMappingCompleted += (oov, eev) =>
                        {
                            pass = true;
                        };

                    client.SaveMappingAsync(_mappingDetail, SavedDefaultValues);


                    while (!pass)
                        Thread.Sleep(0);
                };

            bgw.RunWorkerCompleted += (ov, ev) =>
            {
                client.CloseAsync();
                var Impclient = AEnv.GetExcelImportService();
                Impclient.LoadDefaultValuesCompleted += (_sender, ee) =>
                {
                    Impclient.CloseAsync();
                    var ImpClientMap = AEnv.GetExcelImportService();
                    ImpClientMap.LoadMappedInfoCompleted += (se, eve) =>
                        {
                            ImpClientMap.CloseAsync();
                            if (eve.Result != null)
                            {
                                _mappingDetail = eve.Result;
                            }

                            LoadMappedLocationInfo();
                            SetBusy(false, rightUpperGrid);
                            IsImportProcessing(true);

                            ShowMessage.Info("ImportMappingSaved", true, "", "");
                            return;
                        };
                    ImpClientMap.LoadMappedInfoAsync(_mappingDetail);
                    if (ee.Result != null)
                    {
                        SavedDefaultValues = ee.Result;
                    }

                };
                Impclient.LoadDefaultValuesAsync(SavedDefaultValues);

            };

            bgw.RunWorkerAsync();
        }

        private void lstDefaultValues_MouseRightButtonUp(object sender, MouseButtonEventArgs e)
        {
            try
            {
                VisualTreeHelper.FindElementsInHostCoordinates(e.GetPosition(null), (sender as ListBox)).OfType<ListBoxItem>().First().IsSelected = true;
            }
            catch { }
        }

        private void lstDefaultValues_MouseRightButtonDown(object sender, MouseButtonEventArgs e)
        {
            e.Handled = true;
        }


        private void RemoveMapping()
        {
            try
            {
                if (localtreeView.SelectedItem == null)
                    return;

                if (((localtreeView.SelectedItem as VTreeNode).Parent as VTreeNode) == null)
                    return;


                if ((((localtreeView.SelectedItem as VTreeNode).Parent as VTreeNode).Parent as VTreeNode) == null)
                    return;

                int index = int.Parse((localtreeView.SelectedItem as VTreeNode).Name);
                int tabIndex = ((localtreeView.SelectedItem as VTreeNode).Parent as VTreeNode).SeqNo;

                ExcelMappingDetail todelete = _mappingDetail.TabDetail[tabIndex].ColumnDetail.FirstOrDefault((a) => a.PrimaryKey == index);
                if (todelete == null)
                    todelete = _mappingDetail.TabDetail[tabIndex].ColumnDetail.FirstOrDefault((a) => a.UniqueKey == index);
                if (todelete == null)
                    todelete = _mappingDetail.TabDetail[tabIndex].ColumnDetail.FirstOrDefault((a) => a.AD_Column_ID == index);

                if (todelete != null)
                {
                    if (todelete.IsIdentifier)
                    {
                        ExcelMappingDetail setidnt = info.AllTables[tabIndex].ColumnDetail.FirstOrDefault((a) => a.IsIdentifier == true);
                        if (setidnt != null)
                            tbIdentifierValue.Text = IDENTIFIER_STRING + setidnt.AD_Column_Name;
                        else
                            tbIdentifierValue.Text = IDENTIFIER_STRING + "None";
                    }

                    if (index < 100)
                        _mappingDetail.TabDetail[tabIndex].ColumnDetail.Remove(todelete);
                    else
                        todelete.ToDelete = true;

                    m_MappedColumns.Remove(todelete.AD_Column_ID);
                    ((localtreeView.SelectedItem as VTreeNode).Parent as VTreeNode).Items.Remove(localtreeView.SelectedItem);
                    SetDBColMappedState();
                }

                //m_MappedColumns.RemoveAt(index);
                //m_MappedTables.RemoveAt(index);
                //m_ExcelColIndex.RemoveAt(index);
                //m_ExcelColName.RemoveAt(index);

                //int selectedParent = ((localtreeView.SelectedItem as VTreeNode).Parent as VTreeNode).SeqNo;
                //int totalParentNodes = (localtreeView.Items[0] as VTreeNode).Items.Count;
                //(localtreeView.SelectedItem as VTreeNode).Name = "deletenode";
                //for (int del = selectedParent; del < totalParentNodes; del++)
                //{
                //    for (int delchild = 0; delchild < ((localtreeView.Items[0] as VTreeNode).Items[del] as VTreeNode).Items.Count; delchild++)
                //    {
                //        if (del == selectedParent)
                //        {
                //            int idx = ((localtreeView.SelectedItem as VTreeNode).Parent as VTreeNode).Items.IndexOf(localtreeView.SelectedItem);
                //            if (delchild <= idx)
                //                continue;
                //        }

                //        //count++;
                //        string matchinIndex = (((localtreeView.Items[0] as VTreeNode).Items[del] as VTreeNode).Items[delchild] as VTreeNode).Name;
                //        if (index < int.Parse(matchinIndex))
                //        {
                //            int newIndex = int.Parse(matchinIndex) - 1;
                //            (((localtreeView.Items[0] as VTreeNode).Items[del] as VTreeNode).Items[delchild] as VTreeNode).Name = newIndex.ToString();
                //        }

                //    }
                //}
                //((localtreeView.SelectedItem as VTreeNode).Parent as VTreeNode).Items.Remove(localtreeView.SelectedItem);
                //m_MapIndex--;
            }
            catch (Exception ex)
            {
                ShowMessage.Error(ex.Message, false);
                return;
            }
        }
        //private void btnRemoveMap_Click(object sender, string  e)
        //{
        //    try
        //    {
        //        if (localtreeView.SelectedItem == null)
        //            return;

        //        if (((localtreeView.SelectedItem as VTreeNode).Parent as VTreeNode) == null)
        //            return;


        //        if ((((localtreeView.SelectedItem as VTreeNode).Parent as VTreeNode).Parent as VTreeNode) == null)
        //            return;

        //        int index = int.Parse((localtreeView.SelectedItem as VTreeNode).Name);
        //        int tabIndex = ((localtreeView.SelectedItem as VTreeNode).Parent as VTreeNode).SeqNo;

        //        ExcelMappingDetail todelete = _mappingDetail.TabDetail[tabIndex].ColumnDetail.FirstOrDefault((a) => a.PrimaryKey == index);
        //        if (todelete == null)
        //            todelete = _mappingDetail.TabDetail[tabIndex].ColumnDetail.FirstOrDefault((a) => a.UniqueKey == index);
        //        if(todelete == null)
        //            todelete = _mappingDetail.TabDetail[tabIndex].ColumnDetail.FirstOrDefault((a) => a.AD_Column_ID == index);

        //        if (todelete != null)
        //        {
        //            if (todelete.IsIdentifier)
        //            {
        //                ExcelMappingDetail setidnt = info.AllTables[tabIndex].ColumnDetail.FirstOrDefault((a) => a.IsIdentifier == true);
        //                if (setidnt != null)
        //                    tbIdentifierValue.Text = IDENTIFIER_STRING + setidnt.AD_Column_Name;
        //                else
        //                    tbIdentifierValue.Text = IDENTIFIER_STRING + "None";
        //            }

        //            if (index < 100)
        //                _mappingDetail.TabDetail[tabIndex].ColumnDetail.Remove(todelete);
        //            else
        //                todelete.ToDelete = true;

        //            m_MappedColumns.Remove(todelete.AD_Column_ID);
        //            ((localtreeView.SelectedItem as VTreeNode).Parent as VTreeNode).Items.Remove(localtreeView.SelectedItem);
        //        }

        //        //m_MappedColumns.RemoveAt(index);
        //        //m_MappedTables.RemoveAt(index);
        //        //m_ExcelColIndex.RemoveAt(index);
        //        //m_ExcelColName.RemoveAt(index);

        //        //int selectedParent = ((localtreeView.SelectedItem as VTreeNode).Parent as VTreeNode).SeqNo;
        //        //int totalParentNodes = (localtreeView.Items[0] as VTreeNode).Items.Count;
        //        //(localtreeView.SelectedItem as VTreeNode).Name = "deletenode";
        //        //for (int del = selectedParent; del < totalParentNodes; del++)
        //        //{
        //        //    for (int delchild = 0; delchild < ((localtreeView.Items[0] as VTreeNode).Items[del] as VTreeNode).Items.Count; delchild++)
        //        //    {
        //        //        if (del == selectedParent)
        //        //        {
        //        //            int idx = ((localtreeView.SelectedItem as VTreeNode).Parent as VTreeNode).Items.IndexOf(localtreeView.SelectedItem);
        //        //            if (delchild <= idx)
        //        //                continue;
        //        //        }

        //        //        //count++;
        //        //        string matchinIndex = (((localtreeView.Items[0] as VTreeNode).Items[del] as VTreeNode).Items[delchild] as VTreeNode).Name;
        //        //        if (index < int.Parse(matchinIndex))
        //        //        {
        //        //            int newIndex = int.Parse(matchinIndex) - 1;
        //        //            (((localtreeView.Items[0] as VTreeNode).Items[del] as VTreeNode).Items[delchild] as VTreeNode).Name = newIndex.ToString();
        //        //        }

        //        //    }
        //        //}
        //        //((localtreeView.SelectedItem as VTreeNode).Parent as VTreeNode).Items.Remove(localtreeView.SelectedItem);
        //        //m_MapIndex--;
        //    }
        //    catch (Exception ex)
        //    {
        //        ShowMessage.Error(ex.Message, false);
        //        return;
        //    }
        //}

        private void btnNew_Click(object sender, string e)
        {
            _mappingDetail = _mappingDetailOld;
            lstExcelCols.Items.Clear();
            fileArray = null;

            for (int a = 0; a < (localtreeView.Items[0] as VTreeNode).Items.Count; a++)
            {
                ((localtreeView.Items[0] as VTreeNode).Items[a] as VTreeNode).Items.Clear();
            }

            OnNewMode(true);



            cmbMapping.SelectedIndex = -1;
        }

        ExcelMapping _mappingDetailOld = null;

        private void btnCancelNew_Click(object sender, string e)
        {
            _mappingDetail = _mappingDetailOld;
            OnNewMode(false);
        }

        bool IsNewMode = false;
        private void btnSaveAs_Click(object sender, string e)
        {
            if (String.IsNullOrEmpty(txtMappingName.Text))
            {
                ShowMessage.Error(Msg.GetMsg("ProvideMappingName"), false);
                return;
            }

            btnSaveAs.IsEnabled = false;
            int _newMapID = 0;
            loadWindowsDetailsdown.IsEnabled = true;
            loadWindowsDetailsdown.IsBusy = true;
            cmbTable.IsEnabled = false;
            BackgroundWorker bgw = new BackgroundWorker();
            _mappingDetail.MappingName = txtMappingName.Text;
            bool pass = false;

            //_mappingDetail = _mappingDetailOld;
            _mappingDetailOld.ExcelMapping_ID = 0;
            m_MappedColumns.Clear();

            var client = AEnv.GetExcelImportService();
            bgw.DoWork += (ov, ev) =>
                {


                    client.SaveMappingCompleted += (ovv, evv) =>
                        {
                            if (evv.Error == null)
                                _newMapID = evv.Result;

                            //client.CloseAsync();
                            pass = true;

                        };

                    //_mappingDetail.ExcelMapping_ID = 0;

                    client.SaveMappingAsync(_mappingDetail, SavedDefaultValues);



                    while (!pass)
                        Thread.Sleep(0);
                };

            bgw.RunWorkerCompleted += (ov, ev) =>
                {
                    client.CloseAsync();
                    if (_newMapID > 0)
                    {
                        if (_mappingDetail.ExcelMapping_ID <= 0)
                            cmbMapping.Items.Add(new KeyNamePair(_newMapID, txtMappingName.Text));
                    }

                    _mappingDetail.ExcelMapping_ID = _newMapID;

                    SavedDefaultValues.ExcelMapping_ID = _newMapID;

                    btnLoadFile.IsEnabled = true;
                    loadWindowsDetailsdown.IsBusy = false;
                    cmbTable.IsEnabled = true;
                };

            bgw.RunWorkerAsync();
        }

        void OnNewMode(bool isNew)
        {
            IsNewMode = isNew;
            if (isNew)
            {
                tbSelectMapping.Text = Msg.GetMsg("MappingName");
                _import.Clear();
                //lstDefaultValues.Items.Clear();

                //localDefulttreeView.Items.Clear();
                cmbMapping.SelectedItem = -1;
                spSelectMapping.Visibility = System.Windows.Visibility.Collapsed;
                spNewMapping.Visibility = System.Windows.Visibility.Visible;
                loadWindowsDetailsdown.IsEnabled = false;
                cmbTable.IsEnabled = false;
                btnNew.IsEnabled = false;
                btnSaveAs.IsEnabled = true;
                txtMappingName.Text = String.Empty;
            }
            else
            {
                tbSelectMapping.Text = Msg.GetMsg("SelectMapping");
                spSelectMapping.Visibility = System.Windows.Visibility.Visible;
                spNewMapping.Visibility = System.Windows.Visibility.Collapsed;
                loadWindowsDetailsdown.IsEnabled = false;
                cmbTable.IsEnabled = false;
                btnNew.IsEnabled = true;
                btnSaveAs.IsEnabled = false;
            }

        }

        KeyNamePair[] SavedMapping = null;

        /// <summary>
        /// Fetches all the mapping saved by the client
        /// </summary>
        /// <returns></returns>
        KeyNamePair[] GetMapping()
        {
            String _sql = "SELECT Name, AD_ExcelImport_ID From AD_ExcelImport WHERE IsActive = 'Y' AND AD_Window_ID = " + _selectedAD_Window_ID + " ORDER BY AD_ExcelImport_ID ";
            _sql = MRole.GetDefault().AddAccessSQL(_sql, "AD_ExcelImport", false, false);

            List<KeyNamePair> list = new List<KeyNamePair>();
            IDataReader dr = DB.ExecuteReader(_sql);
            while (dr.Read())
            {
                KeyNamePair temp = new KeyNamePair(int.Parse(dr[1].ToString()), dr[0].ToString());
                list.Add(temp);
            }

            dr.Close();

            return list.ToArray();
        }

        private void cmbMapping_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (cmbMapping.SelectedItem == null)
                return;


            if (!IsNewMode)
            {
                _mappingDetail.ExcelMapping_ID = (cmbMapping.SelectedItem as KeyNamePair).GetKey();
                _mappingDetail.MappingName = (cmbMapping.SelectedItem as KeyNamePair).GetName();

                SavedDefaultValues.ExcelMapping_ID = (cmbMapping.SelectedItem as KeyNamePair).GetKey();
                SavedDefaultValues.MappingName = (cmbMapping.SelectedItem as KeyNamePair).GetName();

                loadWindowsDetailsdown.IsEnabled = true;
                cmbTable.IsEnabled = true;

                btnLoadFile.IsEnabled = true;
            }
        }


        void SetCulture()
        {
            tbExcelHeader.Text = Msg.GetMsg("ExcelColumns");
            btnSaveAs.Text = Msg.GetMsg("Create");
            btnMap.Text = Msg.GetMsg("Map");
            //btnRemoveMap.Text = Msg.GetMsg("RemoveMap");
            btnImport.Text = Msg.GetMsg("Import");
            btnSave.Text = Msg.GetMsg("Save");
            chkCommit.Content = Msg.GetMsg("CommitAfterImport");
            btnLoadFile.Text = Msg.GetMsg("Open");
            //  btnUploadFile.Text = Msg.GetMsg("Load");
            vlblTitle.Content = Msg.GetMsg("Import");

        }

        private void btnExportLog_Click(object sender, string e)
        {
            //Open SaveFileDialog window
            SaveFileDialog objSave = new SaveFileDialog();
            // set filter of the window
            objSave.Filter = "All files (*.*)|*.*";
            // set default name of the file to be created

            // set default extension of the file
            objSave.DefaultExt = "txt";

            if (objSave.ShowDialog() == true)
            {
                string fileExt = "";
                // get fullfile name entered by the user in save dialog window
                string[] finalSplit = objSave.SafeFileName.Trim().Split('.');
                if (finalSplit.Length > 1)
                {
                    // get the file extension in case user has selected another file format
                    fileExt = finalSplit[finalSplit.Length - 1];
                }
                //set extension of the file
                objSave.DefaultExt = fileExt;
                if (fileExt == "txt" || fileExt == "doc" || fileExt == "xls" || fileExt == "rtf")
                {

                    StringBuilder content = new StringBuilder();

                    foreach (CallbackDetail c in _import)
                    {
                        content.Append(c.Status);
                        content.Append("\r\n");
                        content.Append(c.Query);

                        content.Append("\r\n\r\n");
                    }


                    System.IO.StreamWriter writer = new System.IO.StreamWriter(objSave.OpenFile());
                    // get log content and writes into stream
                    writer.Write(content);
                    //close the stream
                    writer.Close();

                }
            }


            btnExportLog.IsEnabled = false;
        }
    }
}
